Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: VBA Code To Unmerge Cells On Specific Worksheets

  1. #1
    Join Date
    26th January 2010
    Posts
    6

    VBA Code To Unmerge Cells On Specific Worksheets

    Hello,

    I have a workbook with multiple worksheets. On some of those worksheets a have merged cells, so in order to paste new data I need to unmerge them first. Below is the code that I wrote for it. However it doesn’t work and returns “Run-time error '1004': Application-defined or object-defined error”. I looked at this post http://www.ozgrid.com/forum/showthread.php?t=87865 and tried adding With – End With, but it failed too. As far as I understood the problem is that the Worksheets/Sheets object does not support UnMerge property, but I may be wrong. If I add ws.Activate line (test2) everything works fine. However I was wondering if there is a way to do it WITHOUT activating the worksheets. Thank you for your help!

    Code:
    Sub test1()
        Dim i As Integer, ws As Worksheet
        
        For Each ws In ActiveWorkbook.Worksheets
            Select Case ws.Name
                Case "5 - Top Network Facilities", _
                    "5b - Top Arb Facilities"
                    For i = 0 To 9
                        ws.Range(Cells(2 + i * 5, 1), _
                            Cells(6 + i * 5, 1)).UnMerge
                    Next i
                Case Else
            End Select
        Next ws
    End Sub
    Code:
    Sub test2()
    …
                    For i = 0 To 9
                        ws.Activate
                        Range(Cells(2 + i * 5, 1), _
                            Cells(6 + i * 5, 1)).UnMerge
                    Next i
    …
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th March 2009
    Location
    Vienna, VA
    Posts
    43

    Re: Perform Actions On Not Active Worksheet (cycling Through Worksheets W/vba)

    I was not able to replicate your problem. I set up a simple workbook with two worksheets. I merged two cells in one of them, and was able to execute code that unmerged the cells when that sheet was not active.

    Code:
    Public Sub trymerge()
    
       Sheet2.Activate
       
       If Sheet1.Range("B3").MergeCells Then
          Sheet1.Range("B3").UnMerge
       Else
          MsgBox "Not merged"
       End If
       
    
    End Sub
    This code is located in a Module. I wonder if the location of your code has something to do with it. Where is the code located relative to the workbook and worksheets you are manipulating?
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會

    [code]
    ' Enclose code in tags like this
    [/code]

    Don't attach a screenshot
    --just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  3. #3
    Join Date
    27th January 2010
    Posts
    2

    Re: Perform Actions On Not Active Worksheet (cycling Through Worksheets W/vba)

    Hello,
    I am not sure if I understood your problem correctly, but here is the code that does the same as yours but WITHOUT selecting/activating a worksheet.
    Hope, this will be help.

    Code:
    Sub test1()
        Dim i As Integer, ws As Worksheet, rng As Range
         
        For Each ws In ActiveWorkbook.Worksheets
            Select Case ws.Name
            Case "5 - Top Network Facilities", _
                "5b - Top Arb Facilities"
                For i = 0 To 9
                    Set rng = ws.Cells(2 + i * 5, 1).MergeArea
                    With rng
                        If .MergeCells Then
                            .Cells(1, 1).UnMerge
                        End If
                    End With
                Next i
            Case Else
            End Select
        Next ws
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2010
    Posts
    6

    Re: Perform Actions On Not Active Worksheet (cycling Through Worksheets W/vba)

    Shinny76, you are a genius. It worked perfectly. The case is solved. Thank you very much.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    27th January 2010
    Posts
    2

    Re: Perform Actions On Not Active Worksheet (cycling Through Worksheets W/vba)

    You are welcome. I am glad I was able to help.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Unmerge And Spread Merge Data To All Cells
    By texan2000 in forum EXCEL HELP
    Replies: 5
    Last Post: April 14th, 2012, 10:06
  2. Replies: 9
    Last Post: July 19th, 2011, 01:51
  3. Unmerge Merged Cells
    By sassy2 in forum EXCEL HELP
    Replies: 5
    Last Post: June 15th, 2008, 09:58
  4. Add-in Function To Format & Unmerge Cells
    By akantheri in forum EXCEL HELP
    Replies: 10
    Last Post: February 29th, 2008, 03:18
  5. Color Code Matching Cells Across 2 Worksheets
    By ramisst in forum EXCEL HELP
    Replies: 4
    Last Post: August 29th, 2007, 15:49

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno