Excel VBA run time error 1004 method range of object _worksheet failed

Question

I'm really in need of help to pinpoint what's wrong on my excel vba. Totally beginner here.

Basically the the vba code will do "filter" for the worksheet. The weird things is that when filter on other categories, the vba works. But on 1 category, it just keep giving me run-time error 1004 (method range of object _worksheet failed).

Debug mode will always pinpoint to the line of code:

Range(Mid(rangeToHide, 1, 199)).Select

Here is the code:

Private Sub cboPopulateDept_Change()
Dim sh As Worksheet
Dim rw As Range
Dim RowCount As Integer
Dim rangeToHide As String
Dim emptyRow As Integer

unHide

If cboPopulateDept.Value = "ALL" Or cboPopulateDept.Value = "" Then
    Exit Sub
End If

RowCount = 1

Set sh = ActiveSheet
For Each rw In sh.Rows
    If RowCount >= 6 Then
        If sh.Cells(RowCount, 1).Value Like "TOP Innovation Projects - Vision 2020 - Participating?" Then
            Exit For
        End If

        If sh.Cells(RowCount, 3).Value <> cboPopulateDept.Value And sh.Cells(RowCount, 3).Value <> "" Then
            'sh.Cells(RowCount, 3).EntireRow.Hidden = True
            'sh.Cells(RowCount + 1, 3).EntireRow.Hidden = True

            rangeToHide = rangeToHide & RowCount & ":" & RowCount + 1 & ","
            RowCount = RowCount + 2
        Else
            RowCount = RowCount + 1
        End If
    Else
        RowCount = RowCount + 1
    End If

Next rw

rangeToHide = Mid(rangeToHide, 1, Len(rangeToHide) - 1)
 If Len(rangeToHide) <= 201 Then
    Range(rangeToHide).Select
    Selection.EntireRow.Hidden = True
Else
    Range(Mid(rangeToHide, 1, 199)).Select
    Selection.EntireRow.Hidden = True

    Range(Mid(rangeToHide, 201, Len(rangeToHide))).Select
    Selection.EntireRow.Hidden = True
End If

'Range(rangeToHide).Select
'Selection.EntireRow.Hidden = True

Range("A8:A9").Select

End Sub

Thanks

Cheers, RH

Press Ctrl+G when it stops and in the widow that opens up write "?Range(Mid(rangeToHide, 1, 199)).address" without quotes.What does it return ? — Imran Malek

QHarr QHarr · Accepted Answer · 2017-10-24T06:14:18

You are probably not selecting a valid range with:

Range(Mid(rangeToHide, 1, 199)).Select

As rangeToHide is declared as a string, unless Mid(rangeToHide, 1, 199) resolves to a valid range address such as "A3" then you won't be able to use it within Range(myexpression).Select. Put a watch on this expression and have it stop on change of value. Then run the code and check the value of the expression at the point it changes when using the filter in question.

You also should be using VBNullString instead of ' ' and Long instead of integers. Also, use option explicit at the top to check you have declared all your variables and that they are used. And explicitly reference the ActiveWorkbook rather than implicitly.

Excel VBA run time error 1004 method range of object _worksheet failed

1 Answers