VBA: Deleting Visible rows using Auto Filter; Excluding Header

[Offroadracer_814]

Am a basic VBA user. I mainly use the record function to create my macros and then update as much as possible. I am creating a report at work that takes the same repetitive steps. So I am wanting to macro as much as I can. But, I do not know how to macro to Delete Visible Row using the Auto Filter. Excluding the header. (I need to keep the header for two pivot tables. Which, can we use a macro to create Pivot tables?)
In column Eight, I need to remove all rows that there is no data in that column. Then in Column 18, I need to remove all rows that has a 1 in that column.

Below is my current VBA/Macro.

Thanks in advance.

Kevin

Sub B_N_B_2_2()
'
' B_N_B_2_2 Macro
'

'
    ActiveSheet.Name = "Black-N-Blue Report"
    Dim Lrow As Long, Lcol As Long
    Lrow = Range("A" & Rows.Count).End(xlUp).Row
    Lcol = Cells(1, Columns.Count).End(xlToLeft).Column
    Cells.Select
    ActiveWorkbook.Worksheets("Black-N-Blue Report").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Black-N-Blue Report").Sort.SortFields.Add Key:= _
        Range("H2:H" & Lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("Black-N-Blue Report").Sort.SortFields.Add Key:= _
        Range("E2:E" & Lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("Black-N-Blue Report").Sort.SortFields.Add Key:= _
        Range("G2:G" & Lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Black-N-Blue Report").Sort
        .SetRange Range("A1:S10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1068").AutoFilter Field:=8, Criteria1:="="
    Rows("192:1069").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    ActiveSheet.ShowAllData
    ActiveSheet.Range("$A$1:$S$191").AutoFilter Field:=18, Criteria1:="1"
    Rows("142:185").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ShowAllData
           
End Sub

 

[mumps]

Try:

Sub B_N_B_2_2()
    On Error Resume Next
    Columns("H").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns("R").Replace "1", "#N/A", xlWhole, , False
    Columns("R").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
End Sub

 

[Offroadracer_814]

Try:

Sub B_N_B_2_2()
    On Error Resume Next
    Columns("H").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns("R").Replace "1", "#N/A", xlWhole, , False
    Columns("R").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
End Sub

Thanks for the assistance.

Where in the code would I place this? This is all new to me. Once I learn it. I can use it as reference for other macros.

Thanks again.

Kevin

 

[Offroadracer_814]

Try:

Sub B_N_B_2_2()
    On Error Resume Next
    Columns("H").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns("R").Replace "1", "#N/A", xlWhole, , False
    Columns("R").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
End Sub

I figured out where it went. Thanks again!

It worked as I envisioned.

 

[mumps]

You are very welcome.