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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,070
I have an solution in a workbook , but cant seem to upload the wb.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Roughly how many rows of data do you have on each sheet & of those how many need to be deleted?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
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.
 

ThiccNugg

New Member
Joined
Oct 24, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

New Member
Joined
Oct 24, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Is your data in a structured table? If so what is the name of the table?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,698
Messages
5,765,994
Members
425,322
Latest member
galaxy6623top

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
Top