Macro - Delete Visible Filtered Rows

acolmer

New Member
Joined
Oct 9, 2019
Messages
7
Hi all,

Happy Friday. I'm trying to record a macro that filters a table to any rows with a "D" in the designated row and then deletes the visible rows, unfiltering the table before ending the macro.

I am using the below but it is failing on the "Selection.EntireRow.Delete" command - is anyone able to recommend anything?

Thanks for your help in advance!



Sub Delete()
'
' Delete Macro
'


'
ActiveSheet.Range("$C$5:$T$3456").AutoFilter Field:=1, Criteria1:="<>"
Range("C786").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Rows("1:8").EntireRow.Select
ActiveCell.Activate
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
ActiveSheet.Range("$C$5:$T$3453").AutoFilter Field:=1
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try this
I guess the headings are in row 5

Code:
Sub Delete()
  Range("C5:T" & Range("C" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:="<>"
  ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
  ActiveSheet.ShowAllData
End Sub
 
Upvote 0
Thanks - apologies if this is an overly stupid question but I am a newbie - at what point to I paste this?
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
:cool:
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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