Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    delete all rows which have merged cells (all)

    I need to delete all rows which have merged cells-- Is there a quick way to do it before I do a sort?
    Thanx
    Smbs

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: delete all rows which have merged cells (all)

    How about this? <font color=red>(TEST ON A COPY as it will delete entire rows of data...)</font color=red>

    Steve

    <pre>Option Explicit
    Sub DeleteMergedRows()
    Dim rCell As Range
    Dim rMerge As Range
    For Each rCell In ActiveSheet.UsedRange
    If rCell.MergeCells Then
    If rMerge Is Nothing Then
    Set rMerge = rCell.MergeArea.EntireRow
    Else
    Set rMerge = Union(rMerge, rCell.MergeArea.EntireRow)
    End If
    End If
    Next
    rMerge.Delete
    Set rCell = Nothing
    Set rMerge = Nothing
    End Sub</pre>


  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete all rows which have merged cells (all)

    Without knowing more about your worksheet, quick will have to be a relative term. The only way to do this is to look at every cell in the relevant area to see if it is merged and delete the row when one is found. The code below does this. However, without knowing what your sheet looks like, it must look at all of the cells in the worksheet. On my XL2k (65536 rows and 256 columns), it takes about 8 minutes to run. You can modify it to look at a more reasonable number of rows and columns:

    <code>
    Public Sub DeleteMergedCellRows()
    Dim I As Long, J As Long
    Dim bDelRow As Boolean
    For I = ActiveSheet.Cells.Rows.Count - 1 To 0 Step -1
    Debug.Print I
    bDelRow = False
    For J = 0 To ActiveSheet.Cells.Columns.Count - 1
    If Range("A1").Offset(I, J).MergeCells Then
    bDelRow = True
    Exit For
    End If
    Next J
    If bDelRow Then
    Range("A1").Offset(I, 0).EntireRow.Delete
    End If
    Next I
    End Sub
    </code>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete all rows which have merged cells (all)

    Thanx Steve And Legare--- I adapted both subs to suite my needs and both work just fine
    Regards
    Smbs

Posting Permissions

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