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

 

[nirvana_]

You will have to use something like this.

if left(PCrng.Value,6)="P00000" then

delete the row.

 

[texasalynn]

You will have to use something like this.

if left(PCrng.Value,6)="P00000" then

delete the row.

should be not equal (<>)

if left(PCrng.Value,6)<>"P00000" then

 

[Weaver]

try:

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

It seems like the best way to dynamically delete rows is to start at the bottom

 

[tvman5683]

Sorry to butt in but I have a similar problem but a simpler task.
I want to check each sheet for one cell with a certain value, If present delete and go to the next sheet etc.
Here's the code I've been trying.
Thanksif you can help.

Sub DeleteRow()
Dim Ws As Worksheet
Dim rng As Range
Dim Value As String

For Each Ws In ActiveWorkbook.Worksheets
With Ws
Range("B13").Select
If Cells(13, "B").Value = "Auto forwarded by a Rule" Then
Cells(13, "B").EntireRow.Delete
End If

End With
Next Ws
End Sub


I wa

 

[texasalynn]

Sorry to butt in but I have a similar problem but a simpler task.
I want to check each sheet for one cell with a certain value, If present delete and go to the next sheet etc.
Here's the code I've been trying.
Thanksif you can help.

Sub DeleteRow()
Dim Ws As Worksheet
Dim rng As Range
Dim Value As String

For Each Ws In ActiveWorkbook.Worksheets
With Ws
Range("B13").Select
If Cells(13, "B").Value = "Auto forwarded by a Rule" Then
Cells(13, "B").EntireRow.Delete
End If

End With
Next Ws
End Sub

so what is happening that is not working?

 

[tvman5683]

It doesn't advance to the next sheet.
Thanks

 

[texasalynn]

try changing to this

ws = Worksheets.Count

For i = 1 To ws

...your code
next i

 

[tvman5683]

You mean like this? It errors out om ws = Worksheets.Count
compile error, invalid use of property.



Private Sub DeleteB13()
Dim ws As Worksheets
Dim rng As Range
Dim Value As String

ws = Worksheets.Count
For i = 1 To ws
Range("B13").Select
If Cells(13, "B").Value = "Auto forwarded by a Rule" Then
Cells(13, "B").EntireRow.Delete
End If
Next i

End Sub

 

[texasalynn]

oops - sorry about that

Sub DeleteB13()
Dim ws As Worksheets
Dim rng As Range
Dim Value As String
Dim WSCount as Long

WSCount = Worksheets.Count
For i = 1 To WSCount
Worksheets(i).Select
Range("B13").Select
       If Cells(13, "B").Value = "Auto forwarded by a Rule" Then
       Cells(13, "B").EntireRow.Delete
       End If
Next i

End Sub