application Defined or object defined error

[howard]

I have the following code to delete rows where the value in Col G > 1


when running the macro, I get run time error "application-Defined or object-defined error" and the code below is highlighted

  With Cells(2, 7).Resize(rng.Rows.Count - 1)

See full code below

 Sub DelReconItemsGreaterthan1()

Application.ScreenUpdating = False
 Dim rng As Range, lr As Long
 
        With Sheets("reconciling items")
        
           lr = .Cells(.Rows.Count, "A").End(xlUp).Row
           If .Range("A2") = "" Then
            
           Exit Sub
                               Else
              End If
             
           
           
Set rng = .Range("A1:G" & Cells(.Rows.Count, 1).End(xlUp).Row)
    With Cells(2, 7).Resize(rng.Rows.Count - 1)
        .FormulaR1C1 = "=COUNTIF(R2C6:R" & lr & "C6,RC[-1])"
        
         '.Formula = "=COUNTIF(R2C6:R11C6,RC[-1])"
        .Value = .Value
    End With
    With rng
        .AutoFilter 7, "> 1"
            .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
        End With
        
    Sheets("reconciling items").Select
    
Columns(7).Delete
 
End Sub

It would be appreciated if someone could kindly amend my code

 

[Marcelo Branco]

Try

With .Cells(2, 7).Resize(rng.Rows.Count - 1)

Observe the dot before Cells(2,7)

M.

 

[Marcelo Branco]

The same here before Cells
Set rng = .Range("A1:G" & .Cells(.Rows.Count, 1).End(xlUp).Row)

M.

 

[howard]

Many thanks for pointing this out


Code now runs without any problem

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.