How to dynamically delete rows

[Howdy1]

Hi, everyone,

I am trying to delete many rows in a spreadsheet with over 45000 records. There are 10 columns and column B is the determinant of whether a row should be deleted. The criteria is that if column B contains format like 'P00000xxxx, then it should be retained. Otherwise, the whole row will be deleted. I started to record some codes but then was stuck with the deletion portion. Can anyone help?

Below are the codes that I tried to use:

Dim rng As Range, PCrng As Range, PCstring As String

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Set rng = Range(Selection, Selection.End(xlDown)).Select
For Each PCrng In rng
If PCrng.Value <> " 'P00000xxxx ' I don't know how to search this format, the last 4 digits will vary from case to case.

then delete the row

 

[tvman5683]

Awesome. That did it.
Thank you so much.

 

[Howdy1]

OK, I used the following Deleted Row procedure and the worksheet just seems processing forever

Sub deleteRows()
Dim nextRow As Long
Application.ScreenUpdating = False
For nextRow = Cells(Rows.Count, "B").Row To 2 Step -1
With Cells(nextRow, "B")
If Left(.Value, 6) <> "P00000" Then
.EntireRow.Delete Shift:=xlUp
End If
End With
Next nextRow
Application.ScreenUpdating = True
End Sub


Then I used the other procedures
Sub Delete()
Dim rng as range, pc as range

range("A1:J1").Select
range(Selection, Selection.End(xlDown)).Select

set rng = range(Selection, Selection.End(xlDown))

For pc in rng
IF Left (PC.value, 6) <> "P00000" then
PC.entirerow.delete
end if
next
end sub
------
when I excuted the second procedures, the spreadsheet seems to looping forever, any idea?

Thanks a million.

 

[stanleydgromjr]

Howdy1,

Try:

Option Explicit
Sub DeleteRows_Loop()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 2).End(xlUp).Row
For i = LR To 2 Step -1
  If Left(Cells(i, 2), 6) <> "P00000" Then
    Cells(i, 2).EntireRow.Delete
  End If
Next i
Application.ScreenUpdating = True
End Sub

Have a great day,
Stan

 

[pedy]

Hi,

Im trying to do something similar only that I want to delete the contents of every cell that has "@" in it without deleting the whole row or impacting any other part of my worksheet.