VBA Coding to select cell under Headings when using filter

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
158
I am using a filter to show all "BB" transactions and delete the rest of the rows that are not "BB". Here is what I recorded when I did it:

'Set autofilter on Column E for "BB" and delete all other rows
Range("A1").Select
Sheets("PPDR_BB").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$T$15865").AutoFilter Field:=5, Criteria1:=Array( _
"#N/A", "Overtime", "PAR", "Premiums", "Regular"), Operator:=xlFilterValues
Range("A634").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A634:P634").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.Range("$A$1:$T$633").AutoFilter Field:=5

My question is how can I adjust the coding to delete the rows under the headings that I do not require as the starting row will be variable.

Thanks
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try this

Code:
Sub test()
  Dim sh As Worksheet, lr As Long
  Sheets("PPDR_BB").Select
  Set sh = ActiveSheet
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  lr = sh.Range("E" & Rows.Count).End(xlUp).Row
  sh.Range("A1:T" & lr).AutoFilter Field:=5, Criteria1:=Array( _
    "#N/A", "Overtime", "PAR", "Premiums", "Regular"), Operator:=xlFilterValues
  sh.AutoFilter.Range.Offset(1).EntireRow.Delete
  sh.ShowAllData
End Sub
 
Upvote 0
Try this

Code:
Sub test()
  Dim sh As Worksheet, lr As Long
  Sheets("PPDR_BB").Select
  Set sh = ActiveSheet
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  lr = sh.Range("E" & Rows.Count).End(xlUp).Row
  sh.Range("A1:T" & lr).AutoFilter Field:=5, Criteria1:=Array( _
    "#N/A", "Overtime", "PAR", "Premiums", "Regular"), Operator:=xlFilterValues
  sh.AutoFilter.Range.Offset(1).EntireRow.Delete
  sh.ShowAllData
End Sub

I am getting a compile Error:

Block If without End if. I have tried putting the end if in several places with no success.
 
Upvote 0
Did you copy complete the macro of post #2 ?

Did you modify some of the original macro?
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top