setting a range from Active cell to last cell when Active cell change columns

[Alroj]

Hi Community,

I have this macro that I modified from a previous post. The macro finds a text , in this case, "ABC" and, then from this active cell down to the last cell, it deletes entire rows that contains a value of "1" or "2"
At the moment, the text "ABC" is located in column G but sometimes it is located in another column.

Ideally this macro will operate from the active cell down to the last row (yes, there are black cells down the range). At the moment I have locked column ":g" in the macro below. However, sometimes is column "p" or any other column. Would someone please assist to make this part dynamic? Much appreciated!!

Sub DeletefromActiveCell()

LastRow = Cells(Rows.Count, "d").End(xlUp).Row

  Range("a1:r200").Select
 Selection.Find(what:="ABC", LookIn:=xlValues).Select

 For Each Cell In Range(ActiveCell.Address & ":g" & LastRow)
 If Cell.Value = "1" Or Cell.Value = "2" Then
 Cell.EntireRow.Delete
 End If
 Next


End Sub

 

[jolivanes]

Which line is highlighted when that happens?

 

[jolivanes]

Which line is highlighted when that happens?
Put the values between double quotation marks, as below, and see what happens

BTW, if cell values are numbers, the code should be

If Cells(i, col).Value = 1 Or Cells(i, col).Value = 2 Then Cells(i, col).EntireRow.Delete

If the values are text, the code should be

If Cells(i, col).Value = "1" Or Cells(i, col).Value = "2" Then Cells(i, col).EntireRow.Delete

To convert a value that could be a number, either use CDbl or multiply it by one (1)

 

[jolivanes]

On an trial sheet, I formatted the used range as "General", ran the macro, formatted as "Text", ran the macro and formatted as "Number" and ran the macro.
With all three, it worked as advertised.

If Cells(i, col).Value = CStr(1) Or Cells(i, col).Value = CStr(2) Then Cells(i, col).EntireRow.Interior.Color = vbGreen