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

katamara

New Member
Joined
Mar 16, 2013
Messages
6
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

BengalMagic

Banned user
Joined
Oct 19, 2012
Messages
141
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.

Code:
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
 

BengalMagic

Banned user
Joined
Oct 19, 2012
Messages
141
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

New Member
Joined
Mar 16, 2013
Messages
6

ADVERTISEMENT

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,980
Office Version
  1. 2016
Platform
  1. Windows
Here is a non-looping method you can try that should be pretty fast...
Rich (BB code):
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

New Member
Joined
Mar 28, 2013
Messages
11

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,980
Office Version
  1. 2016
Platform
  1. Windows
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...
Code:
Sub RemoveRowsWithNslashAerrorsInColumnAP()
  On Error Resume Next
  Columns("AP").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,045
Messages
5,639,751
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top