VBA Runtime Error when Declaring/Setting Variables

[Seba Robles]

I’m getting the following error when declaring and setting some variables in my code:

• Runtime error 438 – Object doesn’t support this property or method
• Runtime error 1004 – Application-defined or object-defined error
  
  
  What does the code do?
  1. Clears contents, enters new data to a column, drags down formulas on other columns
  2. Filter column E by “DISCONTINUED” and delete visible rows
  3. Show all data then filter again column E by “CHECK”
  
  

Here’s part of my code

Private Sub UpdateData_Click()
'Declare variable - filter range
Dim LngLastRow As Long
Dim FilterRange As Range

'Set reference up front
With Sheets("PAT")
    LngLastRow = .Range("A7:M" & .RowsCount).End(xlUp).Row
    Set FilterRange = .Range("E7:E" & LngLastRow)
End With

With Sheets("PAT")

[PART 1 OF CODE RUNS HERE]
    
'Part 2 of code begins

    'Filter & Delete Discontinued/Exception codes
    On Error Resume Next
    With FilterRange
        
        'Filter by DISCONTINUED
        .AutoFilter Field:=5, _
                    Criteria1:="DISCONTINUED"

                'Delete visible filtered rows
                    .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
    End With
                
    .ShowAllData
    
    'Filter by CHECK
    With FilterRange
        .AutoFilter Field:=5, _
                    Criteria1:="CHECK"
    End With

End With
End Sub

Also, is the range for Set FilterRange variable correct? Iwant to filter column E but the data goes from column A through M with headersat row 7.


I’m having trouble with executing step 2 of the code. I will appreciate any help you can give and would be grateful if you explain why the errors are occurring for me to learn and better understand.


Thanks in advance!

 

[Osvaldo Palmeiro]

Hi. Try after changing some lines of the code as shown below:

LngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row

.AutoFilter Field:=1, Criteria1:="DISCONTINUED"

.Offset(1, 0).Resize(LngLastRow - 6).SpecialCells(xlCellTypeVisible).EntireRow.Delete

.AutoFilter Field:=1, Criteria1:="CHECK"

 

[Fluff]

How about

Private Sub UpdateData_Click()
   'Declare variable - filter range
   Dim LngLastRow As Long
   Dim FilterRange As Range
   
   'Set reference up front
   With Sheets("PAT")
       LngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
       Set FilterRange = .Range("E7:E" & LngLastRow)
   End With
   
      
   [PART 1 OF CODE RUNS HERE]
       
   'Part 2 of code begins
   
   'Filter & Delete Discontinued/Exception codes
   With FilterRange
      'Filter by DISCONTINUED
      .AutoFilter 1, "DISCONTINUED"
      'Delete visible filtered rows
      On Error Resume Next
      .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      On Error GoTo 0
      .AutoFilter 1
   End With
   
   'Filter by CHECK
   FilterRange.AutoFilter 1, "CHECK"
      
End Sub

 

[Seba Robles]

Thank you both, code is running now! :D

 

[Fluff]

Glad we could help & thanks for the feedback