Macro to delete entire row if first 2 letters in column B = P4

[katamara]

I've seen a few examples of macros to delete the row if the first letter is something, but not if the first 2 is equal to something.
I'm looking to go thru every row that contains data and look in Column B to see if it starts with P4 or P5. If that's true, then delete the entire row.

 

[BengalMagic]

I've seen a few examples of macros to delete the row if the first letter is something, but not if the first 2 is equal to something.
I'm looking to go thru every row that contains data and look in Column B to see if it starts with P4 or P5. If that's true, then delete the entire row.

Sub test()
With ActiveSheet
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
End With
For a = 1 To lastrow
Select Case Left(Cells(a, 2).Value, 2)
Case "P4", "P5"
Rows(a).Delete
End Select
Next
End Sub

 

[Dryver14]

What is the LastCol variable for? or is just programming habit?

 

[BengalMagic]

What is the LastCol variable for? or is just programming habit?

The first four lines (With.......End With) are a generic bit of code to return the last row and last column.

This particular question doesn't need them but there is no time overhead here.

 

[katamara]

Sub test()
With ActiveSheet
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
End With
For a = 1 To lastrow
Select Case Left(Cells(a, 2).Value, 2)
Case "P4", "P5"
Rows(a).Delete
End Select
Next
End Sub

Thanks. I tweaked it just a bit to:
Sub test()
'removal of P4 and P5 on column B
Dim lastrow As Integer
Dim lastcol As String
Dim a As Integer

With ActiveSheet
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
End With
For a = lastrow To 1 Step -1
Select Case Left(Cells(a, 2).Value, 2)
Case "P4", "P5"
Rows(a).Delete
End Select
Next
End Sub

Thank you again it works great.

 

[Rick Rothstein]

Here is a non-looping method you can try that should be pretty fast...

Sub DeleteP4andP5Rows()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  Columns("B").Replace "P4", "=P4", xlPart
  Columns("B").Replace "P5", "=P5", xlPart
  On Error Resume Next
  Columns("B").SpecialCells(xlFormulas).EntireRow.Delete
  On Error GoTo 0
  Columns("B").Replace "=P4", "P4", xlPart
  Columns("B").Replace "=P5", "P5", xlPart
End Sub

NOTE: The last two lines of code (highlighted in red) can be deleted if your data will never have P4 or P5 located past the beginning of the text.

 

[Helen89]

Hi guys,

I have a very similar problem: I want to delete all rows that have "#N/A" in column AP. Only difference to the above is that cells AP contain a Vlookup. Would a macro still work?

BengalMagic, I tried using your script, but I couldn't get it to work...

Would anyone have a solution for that?
Sorry for hijacking the thread, too

 

[Rick Rothstein]

Hi guys,

I have a very similar problem: I want to delete all rows that have "#N/A" in column AP. Only difference to the above is that cells AP contain a Vlookup. Would a macro still work?

As long as #N/A is the only type of error that will ever be in Column AP, you can use this relatively simple macro...

Sub RemoveRowsWithNslashAerrorsInColumnAP()
  On Error Resume Next
  Columns("AP").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

 

[Helen89]

That is GENIUS!!! Thanks a million, Rick!