CODEWITHSUNDEEP
vbaloopsrangecase
Gabriel
Gabriel

Reputation: 13

VBA Range is Nothing

Running the code below and what I'm hoping to see as a result is to have all columns with 0s on row 27 hidden - but depending on the frequency of the data, the range for those columns to be hidden is different. Basically anything that is in a sheet that starts with Daily/monthly/weekly will have to have columns hidden, all the rest of the sheets should be left alone. It worked on a simple workbook using an if statement (sheets starting with X vs. all other), but when I added the case statement it broke...

The line marked down with bold is the one where I get an error: Run-time error '1004' Application-defined or object-defined error

I'm new to this, so please feel free to suggest a good vba tutorials website/book.

Sub Hide_Zero_Columns()

    Dim WS As Worksheet
    Dim Col_to_hide As Range
    Dim Range_to_hide As Range
    Dim X As Integer

    For Each WS In ThisWorkbook.Sheets
        Worksheets(WS.Name).Activate
        With WS
        Select Case Data_Frequency_Sheets
        Case Left(WS.Name, 5) = "Daily"
            Set Range_to_hide = Range("BDV$27:CWH$27")
        Case Left(WS.Name, 5) = "Month"
            Set Range_to_hide = Range("AY$27:CO$27")
        Case Left(WS.Name, 5) = "Weekl"
            Set Range_to_hide = Range("HF$27:NN$27")
        Case Else
            Set Range_to_hide = Range("A1:B1")
        End Select
        Select Case Data_Condition
        Case Left(WS.Name, 5) = "Daily"
            X = 1
        Case Left(WS.Name, 5) = "Month"
            X = 30
        Case Left(WS.Name, 5) = "Weekl"
            X = 7
        Case Else
            X = 999
        End Select
        If X <> 999 Then
                For Each Col_to_hide In ActiveSheet.Range(Range_to_hide) '<-- Error here
                    If UCase(Col_to_hide) = 0 Then
                        Col_to_hide.EntireColumn.Hidden = True
                    Else: Col_to_hide.EntireColumn.Hidden = False
                    End If
                Next Col_to_hide
        End If
    End With
    Next
    ActiveWorkbook.Worksheets("Registrations").Activate
End Sub

Upvotes: 1

Views: 2408

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33145

The error you're getting is because you're passing a Range object as the argument to Activesheet.Range() and it doesn't accept that because of the Range object's default value. It would be like doing this in the Immediate Window

?Range(Range("A1")).Address

You'll get the same error. Whereas with

?Range("A1").Address

You don't. You could do this too

?Range(Range("A1").Address).Address

So the thing is that when you don't specify a property for an object, like Range("A1") instead of Range("A1").Address, then the default property is used. The Range object is a bit strange, but in this case its default property is the Value property. So Activesheet.Range(Range_to_hide) is the same as Activesheet.Range(Range_to_hide.Value). And if Range_to_hide is a multi-cell range, then it's Value property returns an array, which you definitely can't pass into a Range's argument.

That's the explanation, the short answer is simply to use Range_to_hide, like

For Each Col_to_hide in Range_to_hide.Columns

Why the .Columns. Remember when I said that the Range object was a little strange. Well, unlike most objects, it has two default properties based on the context you're using it in. Earlier, the default property was Value, but in the context of a For..Each, the default value is Cells. If you don't specify .Columns in your For..Each, then it will loop through every cell in the Range. In fact, I always specify how the range is aggregated, even if it's the default Cells.

Upvotes: 0

Jason Faulkner
Jason Faulkner

Reputation: 6558

Since you have already defined a Range, you the problem is you are trying to evaluate: Sheet.Range(Range) which throws the error you are getting.

Since it appears you are wanting to iterate across the columns, all you need to do is change the line to this:

' Iterate across the columns in the defined range.
For Each Col_to_hide In Range_to_hide.Columns
    ' Each "Col_to_hide" will represent all cells within the column.
    MsgBox Col_to_hide.Address

Upvotes: 1

Related Questions