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]

How many times would "ABC" be in the used range? More then once?

 

[jolivanes]

Try this.
If you're happy with it, change the ".Interior.Color = vbGreen" to ".Delete"

Sub Maybe()
Dim rw As Long, col As Long, i As Long
rw = ActiveSheet.UsedRange.Find("ABC", , , 1).Row
col = ActiveSheet.UsedRange.Find("ABC", , , 1).Column
    For i = Cells(Rows.Count, col).End(xlUp).Row To rw Step -1
        If Cells(i, col).Value = CStr(1) Or Cells(i, col).Value = CStr(2) Then Cells(i, col).EntireRow.Interior.Color = vbGreen
    Next i
End Sub

 

[Alroj]

Thank yoy for your assistance

Apologies I didn't explain well.

"ABC" is just the key value of the cell and it is unique in the sheet. Once I find "ABC" this cell becomes the "Active Cell". In a way "ABC" is the heading and anything that goes below with value "1" or "2" would be deleted. The detail here is that sometimes there are 50 rows below "ABC" but sometimes is 10 or 100 and within this range some cells could be empty.

 

[DanteAmor]

Try this:

Sub DeletefromActiveCell()
  Dim f As Range
  
  Application.ScreenUpdating = False
  Set f = Cells.Find("ABC", , xlValues, xlWhole, , , False)
  If f Is Nothing Then
    MsgBox "ABC not exists"
  Else
    Range("A" & f.Row, Cells(Rows.Count, f.Column).End(3)).AutoFilter f.Column, 1, xlOr, 2
    ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
    Range("A" & f.Row).AutoFilter
  End If
  Application.ScreenUpdating = True
End Sub

 

[jolivanes]

Did you try the code from Post #3?

 

[Alroj]

Did you try the code from Post #3?

Hi Jolivanes,

I test it and it worked very well. Thank you!!
One question, How would the macro be adjusted to delete only value of "1" or "2" that has been formatted as "general" and not as "numbers"?

 

[Alroj]

Try this:

Sub DeletefromActiveCell()
  Dim f As Range
 
  Application.ScreenUpdating = False
  Set f = Cells.Find("ABC", , xlValues, xlWhole, , , False)
  If f Is Nothing Then
    MsgBox "ABC not exists"
  Else
    Range("A" & f.Row, Cells(Rows.Count, f.Column).End(3)).AutoFilter f.Column, 1, xlOr, 2
    ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
    Range("A" & f.Row).AutoFilter
  End If
  Application.ScreenUpdating = True
End Sub

Gracias DanteAmor, ha!
It also worked very well. Good to know that there are other ways of getting the result I wanted.
Much appreciated!!

 

[jolivanes]

Re Post #7
What happened when you tried it?

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

or

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

 

[Alroj]

Re Post #7
What happened when you tried it?

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

or

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

Jolivanes,

Thank you for your reply.
I tried both options as per your last response and I got the same error message in each case: Run-Time error '13', Type mismatch