0 votes
ago in VBA by Beginner (7 points)

The code below uses autofilter to delete entire rows based on the condition if cells in both Col C AND Col D are blank, then delete the row.

The code is crashing at this line with error msg Runtime error 91 object variable of With block variable not set.

'1. Apply Filter
  ws.UsedRange("$A$1:$D$1000").AutoFilter Field:=3, Criteria1:=""
  ws.UsedRange("$A$1:$D$1000").AutoFilter Field:=4, Criteria1:=""

I've tried using "Range" instead of "UsedRange" and dropping the parentheses containing the range.

The code is designed to be run from the active wb which has a single ws named "Sheet0".

I very much need and appreciate your help resolving the code error. Entire code shown below:

Sub Filter_Del_Visible_Cells()

'Apply a filter to a Range and delete visible rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/


Dim ws As Worksheet
Dim wsActiveSheet As Worksheet
Set wsActiveSheet = Application.ActiveSheet
wsActiveSheet.UsedRange.Clear

  'Set reference to the sheet in the workbook.
'Set ws = ThisWorkbook.Worksheets("Sheet0")
  
'ws.Activate 'not required but allows user to view sheet if warning message appears
  
  'Clear any existing filters
  On Error Resume Next
    ws.ShowAllData
  On Error GoTo 0

  '1. Apply Filter
  ws.UsedRange("$A$1:$D$1000").AutoFilter Field:=3, Criteria1:=""
  ws.UsedRange("$A$1:$D$1000").AutoFilter Field:=4, Criteria1:=""
  
  '2. Delete Rows
  Application.DisplayAlerts = False
    ws.Range("B4:G1000").SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True
  
  '3. Clear Filter
  On Error Resume Next
    ws.ShowAllData
  On Error GoTo 0

End Sub


 

1 Answer

0 votes
ago by Skilled (360 points)

Hi WyoWindstorrm,

Without making assumptions about the data you are using, here is the corrected code:

Sub Filter_Del_Visible_Cells()

' Cleaned Code

' If the criteria ="" as typed, no rows will be deleted (unless the columns have blanks and that's what you wanted.)

Dim ws As Worksheet

  'Set reference to the sheet in the workbook.
Set ws = ThisWorkbook.Worksheets("Sheet0")
  
'ws.Activate 'not required but allows user to view sheet if warning message appears
  
  'Clear any existing filters
  On Error Resume Next
    ws.ShowAllData
  On Error GoTo 0

  '1. Apply Filter
  ' {this works}: ActiveSheet.Range("$A$1:$H$26").AutoFilter Field:=3, Criteria1:=""
  ws.Range("$A$1:$D$1000").AutoFilter Field:=3, Criteria1:=""
  ws.Range("$A$1:$D$1000").AutoFilter Field:=4, Criteria1:=""
  
  '2. Delete Rows
  Application.DisplayAlerts = False
    ws.Range("B4:G1000").SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True
  
  '3. Clear Filter
  On Error Resume Next
    ws.ShowAllData
  On Error GoTo 0

End Sub

I removed the pasted, commented out code snippets and the wsActiveSheet code, which deletes everything. (If you need that in there, I assume you have your reasons.)

Anyway, UsedRange is the culprit. Just type ws.Range for the two AutoFilter statements.

Disregard the comment beginning '  {this works}

I was not sure if the error was UsedRange or something else, until I tested.

Cheers,

 

Mitch

 

ago by Beginner (7 points)

Mitch, thanks so much for all the time you spent tweaking my code.

I'm still getting an error on this line:

Set ws = ThisWorkbook.Worksheets("Sheet0")
Run-time error '9': Subscript out of range

If you have time, could you please explain what's causing the error and how to fix it.

BTW...

  • The data involved reside in 4 columns A, B, C, D.
  • # of rows varies depending on report run date but never more than 500
  • Data is NOT a table.
  • Each column has a unique header.
  • WB has only 1 sheet with default name Sheet0.
  • In VBA editor Microsoft Excel Objects the sheet is identified as "Sheet1 (Sheet0)
ago by Skilled (360 points)
edited ago by

Hi, WyoWindstorm,

Glad to help.

The error occurs because the "subscript" is undefined. An array has two parts: NameOfArray(IndexIntoArray). The subscript is inside the parentheses and is a reference to one of the elements of the array.

Excel has the ability to translate strings like "Sheet0" into numbers representing the position or order in which array elements are stored. These numbers begin at zero and increment by one until each element in the array has a reference.

You can substitute the word "collection" for "array" and "object" for "element", since we're talking about worksheets.

If the string is not a member of the collection (in this case, worksheets), Excel has no number for it. (I'm not sure about the technical value returned, but in general, a subscript out of range error means that the number in the parentheses is outside of the range 0-n, where n is the number of items in the collection, less 1.)

I can think of two reasons why you got the error:

  1. The name on the worksheet tab is not Sheet0
  2. The name looks like Sheet0 but has a space or the zero is a capital O

Since your last bullet point states that the VBA Editor correctly identifies the worksheet name, I would simply retype the code statement AND retype the worksheet tab name.

 

In rare instances, the workbook can become corrupt. That would require you to do a Clean Project with a 3rd-party tool like MZ-Tools, but I admit, that's a stretch...

 

One last thing, Thisworkbook always refers to objects in the same workbook as the one where the running code resides. Without seeing your set-up, I have to toss that out there. Make sure you're not running this macro from your Personal Workbook, an add-in, or some other workbook where Sheet0 does not exist.

Cheers,

Mitch

p.s. don't forget to adjust the range in the following statement:

ws.Range("B4:G1000").SpecialCells(xlCellTypeVisible).Delete
since your data is in columns A-D, just to be safe.
ago by Beginner (7 points)
edited ago by

Mitch,

I cannot thank you enough for the marvelous master class in coding you so generously and comprehensively provided.

The part about ThisWorkbook is particularly helpful because, as you implied, the macro WAS saved into my personal.xlsb.

After hours of frustrating web searching, I never came across the constraint that "ThisWorkbook" requires the code to be embedded in the source data wb.

That also explains why I was able to run the code successfully last night in a macro enabled wb that contained the VBA. Now it all makes perfect sense.

If I wanted to keep the macro in personal.xlsb, will changing ThisWorkbook to ActiveWorkbook enable the code to run on whatever workbook is open? This capability is important in my context because each week I export a new, unique .xls file from a database and thus would prefer not to repeatedly embed the macro in the newly generated wb.

Lastly, at risk of sounding naive or sentimental, I truly believe that the world is a better place due to the work of generous souls like you!

 

ago by Skilled (360 points)
edited ago by

Hi WyoWindstorm,

Thank you so much for your heart-felt comment! I appreciate it.

I used to keep functions in Personal.xlsb but then switched to using an add-in. I did a quick test to confirm that you are correct: Activeworkbook in place of ThisWorkbook will allow the macro to run with the currently active open workbook.

By the way, the switch from Personal.xlsb to an add-in is just a personal preference. :)

Cheers,

Mitch

 

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...