Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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!

    VB:
    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 
    
    
    VB:
    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.

    VB:
    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.

    VB:
    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