Deleting rows macro

[RichJW]

Hi,
I moved a working macro from one spreadsheet to another andit has now stopped working. In both spreadsheets, the data is in the same rowsand columns, with the exception of there now being one more column (L).
The data starts on row 5, with the headings on row 4. Theamount of rows differs each time I run the macro.
The macro previously found anything in column A with either “Apples”,“Bananas”, or “Plums” and deleted the entire row. Now it leaves the rows in thespreadsheet but throws up no errors.
Another user helped me with the macro, hence I am strugglingto decipher it.
The part of the macro not working is this…

lastrow =Cells(Rows.Count, 1).End(xlUp).Row
v1 =ActiveSheet.Range(Cells(1, 1), Cells(lastrow, 27)).Value2
bUnion = False
For i = 1 Tolastrow
If v1(i, 1) ="Apples" Or v1(i, 1) = "Bananas" Or v1(i, 1) = "Plums"Then
If bUnionThen
Set r1= Union(r1, Cells(i, 1))
Else
Set r1= Cells(i, 1)
bUnion= True
End If
End If
Next i

Thanks,
Rich

 

[Logit]

.
Can't say I understand the macro you have posted. Sorry.

However, this macro will review Column A from Row 5 to the last row, identify where any of the three terms exist and delete that entire row.

Option Explicit


Sub DeleteMyRows()


Dim Rng As Range, Cell As Range
Dim lr As Long, i As Long
lr = Range("A" & Rows.Count).End(xlUp).Row




 For i = 5 To lr
    
     If Range("A" & i) = "Apples" Or Range("A" & i) = "Bananas" Or Range("A" & i) = "Plums" Then
        Range("A" & i).EntireRow.Delete
     End If


Next i




End Sub

 

[Peter_SSs]

However, this macro will review Column A from Row 5 to the last row, identify where any of the three terms exist and delete that entire row.

.. but not reliably. Try running it after entering "Apples" in A5:A8.
To remove rows individually like that you need to work from bottom to top.

For i = lr to 5 step -1

Rich
About how many rows of data might you have altogether?

 

[Logit]

.
Yes sir ... you are absolutely correct. Thanks for the catch.

 

[RichJW]

Thanks both, appreciate your help.

Peter, it will be between 10 and 1000.
Which line of text do I need to replace with the one Peter provided?

Rich

 

[RichJW]

Ah, got it.
The macro threw up an error on the second line of text, however once I removed the "i As Long" from the end it ran fine.
Thanks a lot for your help both of you.

Rich

 

[Peter_SSs]

Rich
Can you post the code that you ended up using?

 

[Peter_SSs]

Ah, got it.
The macro threw up an error on the second line of text, however once I removed the "i As Long" from the end it ran fine.

On looking back over the thread, I think that you may have been editing the wrong line. The one I was referring to in post 3 is the red one below.

Option Explicit

Sub DeleteMyRows()

Dim Rng As Range, Cell As Range
Dim lr As Long, i As Long
lr = Range("A" & Rows.Count).End(xlUp).Row


 [COLOR="#FF0000"][B]For i = 5 To lr[/B][/COLOR]
    
     If Range("A" & i) = "Apples" Or Range("A" & i) = "Bananas" Or Range("A" & i) = "Plums" Then
        Range("A" & i).EntireRow.Delete
     End If


Next i


End Sub

 

[RichJW]

Hi Peter, I used this...

Dim Rng As Range, Cell As Range
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row


For i = lr To 5 Step -1

If Range("A" & i) = "Apples" Or Range("A" & i) = "Bananas" Or Range("A" & i) = "Plums" Then
Range("A" & i).EntireRow.Delete
End If

 

[RichJW]

The macro you have just posted fails at Dim lr As Long, i As Long

It shows a compile error duplicate declaration in current scope