Subscript out of range VBA

[excelerino]

Hi all,

I'm trying to get rows with errors automatically deleted whenever a particular sheet is activated. The following code, most of which I got online, give me a subscript out of range error on the bolded line. I saw there was a similar thread earlier today, but I just don't understand the problem

Private Sub Worksheet_Activate()
Dim rng As Range
Dim rngError As Range
Set rng = Worksheets("Sheet7")
On Error Resume Next
Set rngError = rng.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not rngError Is Nothing Then
rngError.EntireRow.Delete
End If
End Sub

Thanks in advance!

 

[steve the fish]

You are trying to set a sheet to a variable you have said in your Dim is a range.

 

[Norie]

You've declared rng as a Range but then here you try and assign it to a worksheet, that's not going to work.

Which sheet do you want to delete rows from?

Which range on that sheet do you want to check for errors?

 

[excelerino]

Ok. I first had it as the following, in which I just changed the range values

Set rng = Sheets("Sheet7").Range("A1:DC461")

But basically I want it to check the entirety of Sheet7 as more data will be added

 

[Big Wade]

Try something like this.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row
If Sh.Name = "Sheet7" Then
    For j = lastRow To 1 Step -1
        If IsError(Range("A" & j)) Then Rows(j).Delete
    Next j
End If
End Sub

Change the Sh.Name and Range column to match your preferences

 

[Norie]

Which sheets module is the code in?

 

[excelerino]

@Big Wade Thanks. For some reason nothing happens though when that code is in place?

@Norie I put the code in Sheet7

 

[Big Wade]

put that code in the ThisWorkbook object sheet

 

[Norie]

Try this.

Private Sub Worksheet_Activate()
Dim rng As Range
Dim rngError As Range

    Set rng = Me.UsedRange ' Me refers to the sheet the module belongs to

    On Error Resume Next
    Set rngError = rng.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    On Error GoTo 0

    If Not rngError Is Nothing Then
        rngError.EntireRow.Delete
    End If

End Sub

 

[excelerino]

That did the trick. Thanks a lot to you both!