VBA code HELP - filter sheets and delete unwanted rows

ThiccNugg

New Member
Joined
Oct 24, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi, I am not very proficient with VBA code (hence why I am posting this).

So I have a workbook that automatically copies and pastes all the data on the first sheet to every other sheet when the first sheet has data pasted into it.

What I need now and can't figure out the most efficient way to do it, is to filter the data on each sheet depending on a cell value (AZ1) in each sheet (this cell value is determined through the UNIQUE function which is from the data in the originals AC column).

So for each sheet I need to filter the data on that sheet via the designated cell value for that sheet.

I have figured out how to do so easily with this code:

Sub AutoFilter()

Dim ws As Worksheet

For Each ws In Sheets
If ws.Name <> "BCA" _
And ws.Name <> "BH AIR" _
And ws.Name <> "BH ROADS" _
And ws.Visible = True Then
ws.Activate

Range("AC2").AutoFilter field:=29, Criteria1:=Range("AZ1").Text


End If
Next ws
Call DeleteBlankRows

End Sub

However, I want the filtered out/hidden data to be deleted leaving only the rows that contain the AZ1 value in their AC column. From what I know deleting the hidden data from each sheet is very slow considering the large amount of data I'm using, so I assume an easier method would be to hide my "wanted rows" and then delete the visible rows and then unhide the data, leaving only my wanted rows. This is the code I cannot seem to figure out or get to work.

If a range is needed in the code it would need to be worked out automatically as the sheet would be used daily to filter the raw data into sheets and therefore the amount of data is different each day.

I am unsure if I have explained what I require clearly but some assistance would be much appreciated.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Roughly how many rows of data do you have on each sheet & of those how many need to be deleted?
 
Upvote 0
Assuming you have a header row in row 2 & there are not too many disjointed rows to delete, try
VBA Code:
Sub ThiccNugg()
   Dim ws As Worksheet
   
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   
   For Each ws In Worksheets
      If ws.Visible = xlSheetVisible Then
         Select Case ws.Name
            Case "BCA", "BH AIR", "BH ROADS"
            Case Else
               ws.Range("A2:AC2").AutoFilter 29, "<>" & ws.Range("AZ1").Text
               ws.AutoFilter.Range.Offset(1).EntireRow.Delete
               ws.AutoFilterMode = False
         End Select
      End If
   Next ws
   Application.Calculation = xlCalculationAutomatic
End Sub
If this works, but takes a long time, there are ways to do it faster.
 
Upvote 0
Roughly how many rows of data do you have on each sheet & of those how many need to be deleted?
Approximately 2000 rows per sheet that then needs to be filtered and about 10-15 sheets (spreadsheet is used daily)
 
Upvote 0
Assuming you have a header row in row 2 & there are not too many disjointed rows to delete, try
VBA Code:
Sub ThiccNugg()
   Dim ws As Worksheet
  
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
  
   For Each ws In Worksheets
      If ws.Visible = xlSheetVisible Then
         Select Case ws.Name
            Case "BCA", "BH AIR", "BH ROADS"
            Case Else
               ws.Range("A2:AC2").AutoFilter 29, "<>" & ws.Range("AZ1").Text
               ws.AutoFilter.Range.Offset(1).EntireRow.Delete
               ws.AutoFilterMode = False
         End Select
      End If
   Next ws
   Application.Calculation = xlCalculationAutomatic
End Sub
If this works, but takes a long time, there are ways to do it faster.
Yeah that seems like it should work but flashes the same error I keep getting which is "cant move cells in a filtered range or table"
 
Upvote 0
Is your data in a structured table? If so what is the name of the table?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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