Excel Tutorial: How to use VBA to delete filtered rows (fast!!) ... but not delete hidden rows

// FREE Excel E-book "Record Your First Macro"
→ www.launchexcel.com/record-yo...
// Recommended Excel Courses //
1. Launch Excel Macros & VBA School: go.launchexcel.com/macros-vba...
2. Excel Campus Elevate Program: go.launchexcel.com/excel-camp...
3. My Online Training Hub Dashboards: go.launchexcel.com/moth-dashb...
4. Coursera Excel Specialisation: go.launchexcel.com/coursera-e...
5. Coursera Everyday Excel: go.launchexcel.com/coursera-e...
6. Coursera Excel Data Visualisation: go.launchexcel.com/coursera-d...
// Recommended Excel Templates //
If you are looking for pre-built, easy to use spreadsheets, check out Simple Sheets.
go.launchexcel.com/simple-she...
They have Excel templates covering cash flow planning, invoices, expense reports, personal budgets, and more!
DOWNLOAD WORKBOOK → www.launchexcel.com/media/sam...
--
// EPISODE NOTES
In this tutorial video we look at how to delete (or remove) filtered rows in your Excel spreadsheets using the built-in programming language VBA (Visual Basic for Applications).
I walk through two sample VBA macros.
VBA Macro 1 - Applies two filter criteria to your worksheet autofilter, then deletes the VISIBLE filtered rows.
VBA Macro 2 - Applies two filter criteria to your worksheet autofilter, then deletes the HIDDEN rows.
Please follow these instructions to learn from the workbook download:
▸ Download the sample Excel workbook: www.launchexcel.com/media/sam...
▸ Open the workbook and make sure to enable macros
▸ Press ALT + F11 to open the VBA Editor
▸ Use F8 to step through the code one line at a time
▸ I suggest you adapt this to your own needs... just copy and paste the code, then change the code to work for you
▸ If you have a viewer request, leave me a comment below and I'll try to solve your problem and create a new video
--
// REFERENCES
Excel Version: 2016 (used in video)
--
// VISIT MY WEBSITE
For more tutorials on Microsoft Excel and VBA visit the website
→ www.launchexcel.com

[@launchexcel]

DOWNLOAD WORKBOOK → www.launchexcel.com/media/samples/VBA/20180118-Delete-Filtered-Rows.xlsm
If you're totally new to Excel Macros & VBA... get my FREE e-book at this link:
DOWNLOAD EBOOK → launchexcel.lpages.co/first-macro-ebook
Leave comments with any questions you have...
And click on the LIKE button if you found this video helpful.
Thanks for supporting my channel!

[@kokhoongkan7623]

Highlights:
4:00 Start of code

[@erdiaz39]

Awesome, thanks for the tip, 2nd macro helped me a lot

[@rajeshmajumdar4999]

Excellent!!!

[@eduardooc8531]

well done! nice and clean code

[@cryptic6969]

Thank you so much really this is amazing and just what i needed

[@bobbobby8699]

Thank you so much, one of the BEST VBA pple on youtube. Thx alot really really helpful

[@launchexcel]

Great to hear!

[@exceleratelearning4529]

Awesome tutorial!

[@launchexcel]

Hi Excelerate Learning! Thanks for your comment.

[@kristinefrancesbaldas6306]

This is very helpful. Thank you. How about if I want to run this all on all worksheets?

[@ardaarsen]

very efficient for loop. It iis good to use union instead of delete

[@zacksvoboda6050]

Thank you very much. I was practicing deleting filtered rows in a workbook and I forgot the "SpecialCells" property. To my surprise, only the visible rows were deleted anyway. I ran the same test several times in various ways, and still myRange.Offset(1, 0).EntireRow.Delete acts only on the visible rows.
Do I not need to use SpecialCells? Why does every online tutorial use SpecialCells to delete filtered rows if it isn't necessary?

[@Hgiel46137]

What is the code if you want to use two filters from the same column? I tried adding a second line like in the video but using the same field number but I got a "named argument not found" error. I also tried adding multiple words in the criterial section but that didn't work either.

[@ManojKumar-pp3fy]

Hey Hgiel, u have to apply criteria2 with or operator

[@humayyilmaz3641]

ws.Range("O1").AutoFilter _
Field:=15, _
Criteria1:="N/A"
ws.Range("A2:Y" & lr).EntireRow.Delete
when the file doesn't find N/A it deletes all the not selected rows. could you please help me with this? vba delete rows as the criteria not met and can't be found.

[@girishankar09]

I get an error stating Run time error '1004'
This cant be done on multiple range selection.
select a single range and try again
Sub Test2()
Dim Rng As Range
Dim Rng_Del As Range
Set Rng = Range("A1").CurrentRegion
Rng.AutoFilter Field:=16, Criteria1:="Complete"
Rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Cut
Sheets("Completed Archive").Select
Range("Completed_Archive[[#Headers],[Stack Rank]]").Select
Selection.End(xlDown).Select
If ActiveCell = "" Then
ActiveSheet.Paste
Else
ActiveCell.Offset(1).Activate
ActiveSheet.Paste
End If
End Sub
could you please look into it

[@christianprag]

It doesn't seem to work, when I press the button in the workbook. Dkw.

[@kebabbert]

Where is the code for Reset_Sheet?

[@randyrobertson6093]

in Sub Keep_CD_Blanks() when, and where, is the range myCell initialized? When I downloaded and ran if FAILED!!!
The first fail was sub Reset_Sheet, line 42, Range("A1").Select
With wks_Working
.Range("A2:C14").Value = "-"
.Range("E2:I14").Value = "-"
.Range("A1").Select
End With
Second time I ran the sub, I commented out .R .Range("A1").Select
and added set myCell = Range("A1") in Sub_Keep_CDs()
Third time I deleted my changes and it ran without errors. Why?

[@diosmiguia]

Excelente!...lo malo es cuando son muchos registros por ejemplo 800000 ...

[@launchexcel]

Thanks Omar! Yes... when there's 800,000 records ... things can get tough.

[@diosmiguia]

Launch Excel asi es normalmente la pc se cuelga y en ese caso no conviene eliminar registros

[@srikanthyadu3367]

@@launchexcel If my Range have large number of rows like 800000,
Solution would be "Deleting 5000 rows 160 times with a wait time so that Excel cannot die(or not respond ) state."
Can you please tell us "How Range can be set to delete like above " instead of "Rng_Del.EntireRow.Delete"
Thanks in advance!!

[@tvdestept5780]

He or she for that?! Really

[@launchexcel]

Yes, I said he or she. I guess "she" is by far the most likely. Anyway... other than the gender issue, I hope the video was helpful.

[@709007]

ub DynamicRange()
'Best used when first column has value on last row and first row has a value in the last column
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Set sht = Worksheets("Sheet1")
Set StartCell = Range("A1")
'Find Last Row and Column
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
'Select Range
sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:="Jan"
.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
End Sub
i am using this code getting an error in the last line in rows.count while deleting pls help