Clear Row based on cell value...

[HURTMYPONY]

Hello,

I was wondering what code to use in my macro that would:

Clear [not delete] every row that has the value "BOOKED" in the A Column, starting on row 4.

I searched and found some VBA that delete rows, but that seems to mess up my total ranges that are on top of the columns on my sheets. Even the fixed formulas ($) seem to lose a row from their range when you actually delete a row from the range. Clearing them does not, and I can sort to remove the spaces...

I know I can name the ranges to keep the total ranges intact, but there are over 100 of them on my 5 source sheets.

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 4 Step -1
    If Range("A" & i).Value = "BOOKED" Then Rows(i).ClearContents
Next i
End Sub

 

[HURTMYPONY]

Thank you!

It works, but is there a way to have it start at Row 5 and not at Row 1?

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 5 Step -1
    If Range("A" & i).Value = "BOOKED" Then Rows(i).ClearContents
Next i
End Sub

 

[HURTMYPONY]

Fan-Freakin'-tastic!

Thank you! Now I will try and decipher why it works, but it is working perfectly.

 

[HalfAce]

Here's another way that doesn't loop and is not case sensitive that might work for you too.

Sub Test2()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
With Range(Cells(5, 1), Cells(LR, 1))
  .AutoFilter Field:=1, Criteria1:="booked"
  .SpecialCells(xlCellTypeVisible).EntireRow.ClearContents
End With
ActiveSheet.AutoFilterMode = False
End Sub

Just another thought. Hope it helps.

 

[HURTMYPONY]

Thank you, HalfAce.

What concerns would I have about it looping? Just process time?

Seems to be working pretty quickly, so I am leaning toward a "if it ain't broke, don't fix it" policy.

Again, thanks to the entire MrExcel Community that have helped me throughout the years appear to be much better at VBA than I ever actually will be. Plus, thank you for the copious amounts of knowledge you have generously given me.

An occasional granule or two of your information actually seeps into my thick head now and then to stay forever and I often surprise myself with what I know, all thanks to this community.