VBA with IF and FOR to delete every row which contains a specific character in column B.

[amit96]

Hello everybody

In VBA Code i need to check if there is any cell in column B that is equal to the letter "S", and if so delete the entire row.
I think it should be with the FOR function since in each time I have a different number of lines.
One time I have 2 lines and another time I have 15 lines.

Thank you

 

[gordsky]

Dim ring as range, cel as range
Set rng = range(“b1:b” & cells(rows.count, 2).end(xlup).row)
For each cel in rng
If cel = “s” then
Cel.entirerow.delete
End if
Next cel

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option without looping ..

Sub Del_Rows()
  With Range("B1", Range("B" & Rows.Count).End(xlUp))
    .Replace What:="s", Replacement:=True, LookAt:=xlWhole, MatchCase:=False
    On Error Resume Next
    .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub

@gordsky
If the OP (or anybody else) copies your code to test, they will get a compile error, due to the quote marks you have used. It would be best if you could actually compose your code in the vba editor (& test) and copy from there to the forum.

Also, I note that you have posted your code in the formula code tags when specific vba code tags are also available.

 

[gordsky]

Hi Peter_SSs,

My come was produced in vba editor and tested and does not produce a compiler error for me with the quotation marks. Did you actually run it? If so be interested to know why it does for you but not on mine.

Point noted re tags which was a genuine mistake.

 

[Alex Blakenburg]

Hi Peter_SSs,

My come was produced in vba editor and tested and does not produce a compiler error for me with the quotation marks. Did you actually run it? If so be interested to know why it does for you but not on mine.

It won't run on my machine either.
The Set rng goes red straight away due to the curly / smart quotes in the Set rng line.
(Compile error. Syntax error)

rng is mispelled in dim ring
(Compile error. Variable not defined)

the cel = "s" also errors out due to the curly quotes.
(Compile error. Variable not defined)

I can't explain how it worked on yours. Unless the curly quotes changed during the copy paste or it is a language thing.
It should have still failed due to the Dim ring (unless you have Option Explicit turned off)

 

[Peter_SSs]

Did you actually run it?

I was not able to run it as posted because, as I noted, the quotation marks that you have used in your post are not the standard 'straight' quotation marks.
Here is what happens if I try to run it.

If I change those quote marks in the highlighted line to 'straight' quote marks the the code runs but it does not delete rows with s in column B unless I also change the other quote marks 2 lines down.

One other thing I noted is that you have Dim ring as Range but in your code used Set rng = ...
Another good example of the benefit of using Option Explicit

 

[gordsky]

It won't run on my machine either.
The Set rng goes red straight away due to the curly / smart quotes in the Set rng line.
(Compile error. Syntax error)

rng is mispelled in dim ring
(Compile error. Variable not defined)

the cel = "s" also errors out due to the curly quotes.
(Compile error. Variable not defined)

I can't explain how it worked on yours. Unless the curly quotes changed during the copy paste or it is a language thing.
It should have still failed due to the Dim ring (unless you have Option Explicit turned off)

View attachment 57848

Hi Alex, thanks for your reply, not sure why they have changed as on my machine they are the standard straight quotations. I also do not have ‘ring’ in my code but rng. Think something has gone wrong somewhere along the copy/paste. Thanks for the feedback

 

[Alex Blakenburg]

Weird, its almost like when you copy from a picture and do a I convert to text. Hopefully it was a one off or that the next restart of the computer sorts it out.