VBA: referencing active cell row in a loop

[bsb2013]

I have a column with jobs, and I am trying to delete every row with job "A". However, I am not sure how to reference the active cell's row in the range function?

basically I'm trying something like this(not working):
If ActiveCell Like "A*" Then
Rows(ActiveCell.row:ActiveCell.row).Select
Selection.Delete Shift:=xlUp
end If

 

[VoG]

Try like this, which is for column A

Sub test4()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("A" & i).Value Like "A*" Then Rows(i).Delete
Next i
End Sub

 

[JackDanIce]

Try:

If ActiveCell Like "A*" Then
  ActiveCell.EntireRow.Delete Shift:=xlUp
End If

Though your question/post is implying you're actively selecting each cell in your column and then testing it if it contains A* and then deleting. You'll find it's not a good idea to use ".Select" alot in your code and it will speed up the execution of your code if you do not. May be an idea to post your full code...

 

[bsb2013]

Try:

If ActiveCell Like "A*" Then
  ActiveCell.EntireRow.Delete Shift:=xlUp
End If

Though your question/post is implying you're actively selecting each cell in your column and then testing it if it contains A* and then deleting. You'll find it's not a good idea to use ".Select" alot in your code and it will speed up the execution of your code if you do not. May be an idea to post your full code...

That would seem logical but now I'm getting an "application-defined or object-defined error" run-time error 1004... any ideas of what could be causing this?

 

[Trevor G]

Try this out, it is using Select and not to slow depending on how many rows it needs to check and clear out, but I do agree with JackDanIce would be better to see your code.

Sub tryme()
Range("B2").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value Like "*A*" Then
ActiveCell.EntireRow.Delete Shift:=xlUp
End If
Loop

End Sub

 

[VoG]

That would seem logical but now I'm getting an "application-defined or object-defined error" run-time error 1004... any ideas of what could be causing this?

Did you miss Post 2 http://www.mrexcel.com/forum/showpost.php?p=2735351&postcount=2