VBA Delete row based on cell value

[dfenton21]

Hi, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have a spread with about 4000 rows. Some cells in column b are blank, and some cells in column b begins with ...<o></o>
<o> </o>
I would like a piece of VBA code that would loop though each row, and delete the row if the cell in column b is blank or begins with …<o></o>
<o> </o>
Is that possible?<o></o>
<o> </o>
Thanks.<o></o>

 

[jeffreybrown]

How about...

Not sure what your begins with is, but in this I set it for anything that begins with B. Update to meet your needs...

Sub DeleteB()
Dim LR As Long

Application.ScreenUpdating = False
    
    For LR = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("B" & LR).Value = "" Or Left(Range("B" & LR), 1) = "B" Then
            Rows(LR).EntireRow.Delete
        End If
    Next LR

Application.ScreenUpdating = True
End Sub

 

[Legacy 287389]

hallo jeffreybrown,

I have been looking for help on this issue, and found this thread. I noticed dfenton21 did not have the decency to thank you for your help. I would like to thank you for this code, it works beautifully!..have a great day..

 

[kalak]

hallo jeffreybrown,

I have been looking for help on this issue, and found this thread. I noticed dfenton21 did not have the decency to thank you for your help. I would like to thank you for this code, it works beautifully!..have a great day..

In fairness to dfenton21, her/his specific request was actually to delete if a cell started with ..., whereas the posted code did not do this.
An easy way to do this sort of thing, which dfenton21 may actually have been looking for is something like

Sub dcfbf()
With Range("B:B")
    .Replace "...*", ""
    .SpecialCells(xlBlanks).EntireRow.Delete
End With
End Sub

If you like, replace the ... by B or whatever you want.

 

[Legacy 287389]

hi kalak,

I understand. The code as was given worked for my purpose, which was to delete rows which contained a certain value. Thanx for your feedback...

 

[BarryL]

I saw Rick Rothstein use something like the below and I've been using it ever since.

Sub Test()
  Dim Addr As String, VarA As Variant, VarB As Variant
  VarA = ""
  VarB = "..."
  Addr = "N1:N" & Cells(Rows.Count, "N").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF((0+@=" & VarA & ")+(0+@=" & VarB & "),@)", "@", Addr))
  Columns("N").SpecialCells(xlConstants, 4).EntireRow.Delete
End Sub

 

[martinka]

Hi Kalak,
Thank you for your VBA code. I achieved my goal to delete rows where there is specific content in a cell in the row B.
Great job!