VBA code HELP - filter sheets and delete unwanted rows

[ThiccNugg]

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.

 

[ranman256]

I have an solution in a workbook , but cant seem to upload the wb.

 

[Fluff]

Roughly how many rows of data do you have on each sheet & of those how many need to be deleted?

 

[Fluff]

Assuming you have a header row in row 2 & there are not too many disjointed rows to delete, try

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.

 

[ThiccNugg]

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)

 

[ThiccNugg]

Assuming you have a header row in row 2 & there are not too many disjointed rows to delete, try

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"

 

[Fluff]

Is your data in a structured table? If so what is the name of the table?