Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

VBA Code To Unmerge Cells On Specific Worksheets

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Comment


    • #3
      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

      Comment


      • #4
        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.

        Comment


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

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

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X