Deleting Rows with VBA

[browncountry]

The code below works as is, but when I there are 40,000 to 50,000 lines of data, it tends to slow down a lot. In the code below, I'm checking for unwanted data in columns A and G. Is there anything I can do to speed this process up? Thanks!

Sub DeleteEntireRow()

Dim RowToTest As Long
With Application
.Calculation = xlCalculationManual
        .ScreenUpdating = False

For RowToTest = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 1)
If .Value = "GRAND TOTAL" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest

For RowToTest = Cells(Rows.Count, 7).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 7)
If .Value <> "P" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

 

[Fluff]

How about

Sub browncountry()
   With ActiveSheet
      .Range("A1:G1").AutoFilter 1, "Grand total"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .ShowAllData
      .Range("A1:G1").AutoFilter 7, "<>P"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[jmacleary]

Well this will reduce the time by about half as it will only loop once through the rows:

Sub DeleteEntireRow()
    Dim LastRow As Long
    Dim RowToTest As Long
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        If Cells(Rows.Count, 1).End(xlUp).Row > Cells(Rows.Count, 7).End(xlUp).Row Then
            LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Else
            LastRow = Cells(Rows.Count, 7).End(xlUp).Row
        End If
        For RowToTest = LastRow To 2 Step -1
            If Cells(RowToTest, 1).Value = "GRAND TOTAL" Then Rows(RowToTest).EntireRow.Delete
            If Cells(RowToTest, 7).Value <> "P" Then Rows(RowToTest).EntireRow.Delete
        Next RowToTest

        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

 

[browncountry]

How about

Sub browncountry()
   With ActiveSheet
      .Range("A1:G1").AutoFilter 1, "Grand total"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .ShowAllData
      .Range("A1:G1").AutoFilter 7, "<>P"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

Wow! That was great.

 

[Fluff]

You're welcome & thanks for the feedback

 

[browncountry]

You're welcome & thanks for the feedback

Fluff, I have one more question. In Column 6, I would like to delete 4 other items. I thought the code below would work, but it didn't. Should I filter and delete each one separately or is there a way to write this on one line?

      .Range("A1:G1").AutoFilter 6, "3B", "SD", "SOM", "TAC"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .ShowAllData

 

[Fluff]

That needs to be like

.Range("A1:G1").AutoFilter 6, Array("3B", "SD", "SOM", "TAC"), xlFilterValues

 

[browncountry]

That needs to be like

.Range("A1:G1").AutoFilter 6, Array("3B", "SD", "SOM", "TAC"), xlFilterValues

Great. Thanks again for all your help.

 

[Fluff]

My pleasure