Excel 2010 VBA - How to - select multiple cells, then select .entirerow for each cell.

[xancalagonx]

What I am trying to do is tidy up an Excel report by deleting all the unwanted rows of data.

Range("1:1").Find(what:="MCCR*resp*", lookat:=xlWhole).Activate
Range(ActiveCell.Offset(1, 0).Address, ActiveCell.End(xlDown).Address).Select
For Each cell In Selection
    If cell <> "ASPF" Then
        firstAddress = cell.Address
        Rng = Rng & cell.Address & ","
        End If
Next cell
If Rng <> "" Then
        Rng = Left(Rng, Len(Rng) - 1)
        Range(Rng).Select
        Selection.EntireRow.Delete
        End If

The Rng is dimmed as String.

What I tried to do is first .find the column and define the range of the selection I want to work with.

Then I run a For...Next loop to identify all the cells with a value other than "ASPF", and add the address of those cells into a string called Rng so all the addresses are listed out as $G$3, $G$4, $G$7,$G$11 and so on.

All of this seems to work.

However, when I attempt to .Select all the cells defined in the Rng, I get an error message.

What I wanted to do was .select all the cells with the unwanted data, then simply expand the selection by using .EntireRow and then .Delete all of the rows leaving me with only the data I need to work with.

The error message I receive is: "Run-time error '1004': Method 'Range' of object '_Global' failed"

Unfortunately I'm not too sharp on Excel code (yet) but I have a suspicion that the Rng which is dimmed as a String gets overflowed with data (since the report has a total of 8700 rows and likely around 5000 of those should be deleted) or that the Rng somehow can't handle the amount of addresses being fed into it from the For...Next.

Anyone know what trips up my code, or how/what to use instead to get it working?

 

[patel45]

If you want delete rows in a range you have to start from the last row, because when you delete a row the range is not longer the same.
"for each" is not the good way, but

lastrow = ......
for j= lastrow to first step -1
.........
next

 

[Norie]

Try this.

Dim cell As Range
Dim rng As String
Dim arrAddresses
Dim I As Long

    Range("1:1").Find(what:="MCCR*resp*", lookat:=xlWhole).Activate


    For Each cell In Range(ActiveCell.Offset(1, 0), Cells(Rows.Count, ActiveCell.Column).End(xlUp))

        If cell <> "ASPF" Then

            rng = rng & cell.Address & ","
        End If
    Next cell

    If rng <> "" Then

        rng = Left(rng, Len(rng) - 1)
        arrAddresses = Split(rng, ",")

        For I = UBound(arrAddresses) To LBound(arrAddresses) Step -1

            Range(arrAddresses(I)).EntireRow.Delete
        Next I
    End If

 

[xancalagonx]

Try this.

Dim cell As Range
Dim rng As String
Dim arrAddresses
Dim I As Long

    Range("1:1").Find(what:="MCCR*resp*", lookat:=xlWhole).Activate


    For Each cell In Range(ActiveCell.Offset(1, 0), Cells(Rows.Count, ActiveCell.Column).End(xlUp))

        If cell <> "ASPF" Then

            rng = rng & cell.Address & ","
        End If
    Next cell

    If rng <> "" Then

        rng = Left(rng, Len(rng) - 1)
        arrAddresses = Split(rng, ",")

        For I = UBound(arrAddresses) To LBound(arrAddresses) Step -1

            Range(arrAddresses(I)).EntireRow.Delete
        Next I
    End If

Thanks Norie, this code worked brilliantly and left me with the data I needed.

Since I'm a shameless "google for the things I need then copy/paste code and adjust it" type of newbie programmer I am always eager to learn exactly what the different parts of the code actually do.

I'll run through with how I think it's working and I'd really appreciate it if you could correct me if I'm mistaken (only way to learn, aside from classes and courses).

rng = Left(rng, Len(rng) - 1) ... 'this simply reduces the length of the string by 1, thus removing the comma separator at the end of the string

arrAddresses = Split(rng, ",") ... 'this populates the arrAddresses array and puts one range value (cell) into each element.

For I = UBound(arrAddresses) To LBound(arrAddresses) Step -1 ... 'now it gets a bit technical for me, but I think this is telling the For...Next to use the Upperbound (highest 'active' element in the array) to the Lowerbound (lowest 'active' element in the array), and work through that list/range backwards by stepping it in increments of -1 (thus starting at bottom of the list in Excel). A question in that regard though, does Ubound = first/top element, i.e. arrAddresses(1), or does it mean the highest number, e.g. arrAddresses(1500)?

Range(arrAddresses(I)).EntireRow.Delete ... 'using this while inside the For...Next will remove the rows one at a time from bottom to top, according to what value I has, based on the Step defined in the code after For (line I listed above).

Hopefully I understood this correctly. Loving how VBA makes my days at work easier (when it's working) so I'm eagerly 'sponging' in any knowledge I can