adding nonconnected cells to selection

[RSXchin]

I'm looping through all rows on a worksheet, and I want to select several cells and then at the end, delete them all.

I CAN'T delete them as I come to them because they are dependent on each other. (Just for fun, I don't want to turn off calculations)

How do I add the rows to selection?

 

[JamesW]

Hi,

Do you mean like this?

Range("A1,A3,A8,A16,A22,A26").Delete

Or is it more like this (delete cells that meet a criteria):

    lRow = Range("A" & Application.Rows.Count).End(xlUp).Row
    For i = lRow To 2 Step -1
        If Cells(i, 1).Value <> 0 Then Cells(i, 1).Delete
    Next

 

[RSXchin]

Deleting cells/rows that meet certain criteria. But I can't delete them as I come to them, I have to delete them all at once.

 

[JamesW]

What difference does it make if you delete them all at once rather than one at a time?

You could maybe try this (not the best way, but it seems to work):

Sub test()
    Dim myRange As Range
    Dim lRow as Double
    Set myRange = Range("A1")
    lRow = Range("A" & Application.Rows.Count).End(xlUp).Row
    For i = lRow To 2 Step -1
        If Cells(i, 1).Value <> 0 Then
            Set myRange = Union(myRange, Range("A" & i))
        End If
    Next
    myRange.Delete
End Sub

 

[RSXchin]

Sorry if I gave a weird example, I'm just trying to find out how to do a "union" of ranges. Until you told me, I had no idea what to call it, let alone implement it.

 

[JamesW]

No problem, I was assuming the example was real. Is it more clear now?

 

[RSXchin]

The problem I'm having now... I don't want to have myRange start out as anything.

 

[RSXchin]

Here we go. This way, MyRange can be empty to begin with

Sub union_test()
Dim MyRange As Range
Set MyRange = Nothing
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 1) = 2 Then
            Set MyRange = Cells(i, 1)
            GoTo 10
        End If
    Next i
    Exit Sub
10: For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 1) = 2 Then
            Set MyRange = Union(MyRange, Cells(i, 1))
        End If
    Next i
    MyRange.EntireRow.Delete shift:=xlUp
End Sub