Is there a VBA to select only visible rows numbered two (2) or higher?

[Mr_Phil]

Hi, I get a data dump from the corporate server. I have manually filtered and deleted the rows I don't need. I decided to record a macro but the output is not quite right because it specifies rows to be selected and those rows might change from day to day as the data shrinks or expands. Is there a way to tell it to simply select the visible rows (except the header) to delete? Today google wasn't real helpful so any ideas or pointers would be great.

Sub PrepGridData()
'
' PrepGridData Macro
'

'
    Range("G7").Select
    ActiveSheet.Range("$A$1:$M$2234").AutoFilter Field:=7, Criteria1:= _
        "DELETE THIS ROW"
    Rows("7:7").Select 
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$M$2101").AutoFilter Field:=7 
    ActiveSheet.Range("$A$1:$M$2101").AutoFilter Field:=4, Criteria1:="<1", _
        Operator:=xlAnd
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$M$90").AutoFilter Field:=4
    Range("F3").Select
    ActiveSheet.Range("$A$1:$M$90").AutoFilter Field:=6, Criteria1:="0"
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$M$26").AutoFilter Field:=6
    Range("A1").Select
End Sub

 

[kevin9999]

It seems to me that you want to delete any row where column D is less than 1 or column F = 0 or column G = "DELETE THIS ROW"? If that's the case, try the following code on a copy of your workbook. Change the sheet name to suit.

Option Explicit
Sub Delete_Multi_Criteria()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    
    Dim LRow As Long, LCol As Long, i As Long
    LRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    LCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
    
    With ws.Range(ws.Cells(2, LCol), ws.Cells(LRow, LCol))
        .FormulaR1C1 = "=IF(OR(RC4<1,RC6=0,RC7=""DELETE THIS ROW""),1,"""")"
        .Value = .Value
    End With
    
    i = WorksheetFunction.Sum(ws.Columns(LCol))
    If i > 0 Then
        ws.Range(ws.Cells(2, 1), ws.Cells(LRow, LCol)).Sort Key1:=ws.Cells(2, LCol), _
        order1:=xlAscending, Header:=xlNo
        ws.Cells(2, LCol).Resize(i).EntireRow.Delete
    End If
End Sub

 

[kevin9999]

Ignore post #2, try this instead

Option Explicit
Sub Delete_Multi_Criteria_V2()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    
    Dim LRow As Long, LCol As Long, i As Long
    LRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    LCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
    
    With ws.Range(ws.Cells(2, LCol), ws.Cells(LRow, LCol))
        .FormulaR1C1 = "=IF(OR(RC4<1,RC6=""0"",RC7=""DELETE THIS ROW""),1,"""")"
        .Value = .Value
    End With
    
    i = WorksheetFunction.Sum(ws.Columns(LCol))
    If i > 0 Then
        ws.Range(ws.Cells(2, 1), ws.Cells(LRow, LCol)).Sort Key1:=ws.Cells(2, LCol), _
        order1:=xlAscending, Header:=xlNo
        ws.Cells(2, LCol).Resize(i).EntireRow.Delete
    End If
End Sub

 

[Peter_SSs]

Is there a way to tell it to simply select the visible rows (except the header) to delete?

To answer your question:
There is no need to select the visible rows only so you just offset the whole range by one row and delete the rows. Only the visible ones will be deleted.

So try this with a copy of your worksheet. It may need a tweak if the actual original range needs to be determined first.

Sub PrepGridData_v2()
  Application.ScreenUpdating = False
  With Range("$A$1:$M$2234")
    .AutoFilter Field:=7, Criteria1:="DELETE THIS ROW"
    .Offset(1).EntireRow.Delete
    .AutoFilter Field:=7
    
    .AutoFilter Field:=4, Criteria1:="<1"
    .Offset(1).EntireRow.Delete
    .AutoFilter Field:=4
    
    .AutoFilter Field:=6, Criteria1:="0"
    .Offset(1).EntireRow.Delete
    .AutoFilter Field:=6
  End With
  Application.ScreenUpdating = True
End Sub

 

[Mr_Phil]

Ignore post #2, try this instead

Option Explicit
Sub Delete_Multi_Criteria_V2()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
   
    Dim LRow As Long, LCol As Long, i As Long
    LRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    LCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
   
    With ws.Range(ws.Cells(2, LCol), ws.Cells(LRow, LCol))
        .FormulaR1C1 = "=IF(OR(RC4<1,RC6=""0"",RC7=""DELETE THIS ROW""),1,"""")"
        .Value = .Value
    End With
   
    i = WorksheetFunction.Sum(ws.Columns(LCol))
    If i > 0 Then
        ws.Range(ws.Cells(2, 1), ws.Cells(LRow, LCol)).Sort Key1:=ws.Cells(2, LCol), _
        order1:=xlAscending, Header:=xlNo
        ws.Cells(2, LCol).Resize(i).EntireRow.Delete
    End If
End Sub

That did it. The only editing I had to do was the worksheet name and remove the quotes from RC6=""0"" since it is a numeric value. Thank you very much for your help.

 

[Mr_Phil]

To answer your question:
There is no need to select the visible rows only so you just offset the whole range by one row and delete the rows. Only the visible ones will be deleted.

So try this with a copy of your worksheet. It may need a tweak if the actual original range needs to be determined first.

Sub PrepGridData_v2()
  Application.ScreenUpdating = False
  With Range("$A$1:$M$2234")
    .AutoFilter Field:=7, Criteria1:="DELETE THIS ROW"
    .Offset(1).EntireRow.Delete
    .AutoFilter Field:=7
   
    .AutoFilter Field:=4, Criteria1:="<1"
    .Offset(1).EntireRow.Delete
    .AutoFilter Field:=4
   
    .AutoFilter Field:=6, Criteria1:="0"
    .Offset(1).EntireRow.Delete
    .AutoFilter Field:=6
  End With
  Application.ScreenUpdating = True
End Sub

This works. I tweaked the range up to 49999 which is the most ever to come from the data dump. I tried to mark it as a solution but the system won't allow two right answers I guess. Thank you very much for the help. It is now in the same module as the other solution so I can check both against each other.

 

[kevin9999]

Glad we could help, and thanks for the feedback

 

[Peter_SSs]

Thank you very much for the help. It is now in the same module as the other solution so I can check both against each other.

You're welcome.

Do you have, or could you have, any empty cells in column D? If so, the two codes could produce different results, one of which would match your original auto-filtering process and one which would not.