0

Code below should delete entire rows based on the condition that cells in BOTH Col C AND D in a given row are empty). The macro uses a filter to display only those rows meeting the aforementioned criteria and then deletes those same visible rows. (h/t Jon Acampora @ excelcampus.com)

The macro runs on a single activebook workbook with one sheet named "Sheet0". In VBA editor, however, this sheet is called "Sheet1" under objects.

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

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

Thanks for your assistance!

NOTE: the code below contains several "commented out" code lines to show my attempts to fix the error. But I'm still getting a Runtime Error 1004 - "This can't be applied to the selected range. Select single cell and try again."

Revised code:

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

Set ws = ActiveSheet

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

'**************Inserting these lines didn't work
'Dim rUsedRange As Range
'Set rUsedRange = Sheets("Sheet1").UsedRange

'************************

'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

    ActiveSheet.Range("A1:D11000").AutoFilter Field:=3, Criteria1:=""
    ActiveSheet.Range("A1:D11000").AutoFilter Field:=4, Criteria1:=""

'ws.UsedRange.AutoFilter Field:=3, Criteria1:=""
'ws.UsedRange.AutoFilter Field:=4, Criteria1:=""

'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
  • Alex M, you are correct and I should have more carefully edited my intro. After repeated failures to achieve working code, I gave up on trying to use the ListObject. Regarding "delete visible", I obtained that code from Jon Acampora's website excelcampus.com. However, I'm willing to try the approach you suggest and also will edit my OP for clarity and relevance. Thanks for calling out my confusing problem description! :) – WyoWindStorm Feb 13 at 20:29
  • Go ahead and edit the question to be a little more clear. – Alex M Feb 13 at 20:31
  • Done. Let me know if the edits have achieved clarity. – WyoWindStorm Feb 13 at 20:58
  • Not really. You say you have error 91 on a single line of code and then you say you have error 1004 on a chunk of different code. Neither problem can be solved using the information you provided (in the first case there's not enough context and in the second you don't say what code is provoking the error); posting both without making clear what your actual problem is only confuses things further. – Alex M Feb 13 at 23:03
  • Copy. Please accept my apology for further muddy-ing the coding waters. – WyoWindStorm Feb 18 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.