Excel vba: Method 'Range' of object '_Global' failed error in excel

Question

I am encountering this error When I use "Let SRange = "C"...." instead, it works but I don't want to specify the end of column like I did as "NV". I'd rater want to use the column in FinalCol. It might be easier to look at the code. I made some changes.. but now it is asking "Object variable or With block variable not set"

     Option Explicit

Sub Range_End()

Dim X As Variant, Count, FinalCol, SRange, XRange As Range

Sheets("Formatted").Select

    X = InputBox("enter row number")


Count = 1
Do While Cells(4, Count) <> Empty
    Count = Count + 1
Loop
    FinalCol = Count - 1

  '  Let SRange = "C" & X & ":" & "NV" & X
SRange = Range(Cells(X, 3), Cells(X, FinalCol)).Address

     Application.ScreenUpdating = False
       For Each XRange In Range(SRange)
           If XRange.Value = "" Then
               XRange.EntireColumn.Hidden = True
           Else
                XRange.EntireColumn.Hidden = False
            End If
        Next XRange

    Application.ScreenUpdating = True

End Sub

Try `SRange = Range(Cells(X, 3), Cells(X, FinalCol)).Address` — A.S.H, May 23 '17 at 16:34
Also, you should always declare your variables. Add `Option Explicit` to the very top (even above `Sub Range_End()`, then add the variables, i.e. `Dim X as Long`, `Dim SRange as String` (as you have it, you're looking for a string, but perhaps look in to making that a `Range` instead), etc... — BruceWayne, May 23 '17 at 16:36
Or better declare SRange as `Range`, then - `Set SRange = Range(Cells(X, 3), Cells(X, FinalCol))` and then loop over `SRange` directly. There's no need to convert a range to its address and then use that address to convert back to the exact same range. — Tim Williams, May 23 '17 at 16:36
@A.S.H Could you help me edit the code? i tried your method, it doesn't work:( — stackquestionfdsaf, May 23 '17 at 16:50
@TimWilliams I made some changes but it doesn't seem to make any improvements:( — stackquestionfdsaf, May 23 '17 at 16:50
It won't run as you haven't declared `Count` or `FinalCol` properly. Also, if you're trying to declare a range use `Set` not `Let` — Tom, May 23 '17 at 16:52
@JinPyoRha Please have a look at my answer. If you don't give them a data type in vba it will just set them as a variant which uses more memory and can cause issues — Tom, May 23 '17 at 16:57
The `Let` keyword has been obsolete for a loooooooooong time. *Value* assignments don't need it. Also be careful with implicit `ActiveSheet` references; read [**this**](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) before it bites you. — Mathieu Guindon, May 23 '17 at 17:06
@Tom you need `Set` for *reference* assignments. You'll see `Let` in `Property Let` procedures, but that's not a value assignment expression, it's a property member declaration, and it doesn't have to be in a class module. Value assignment doesn't need a `Let` keyword. — Mathieu Guindon, May 23 '17 at 17:09

Tom · Answer 1 · 2017-05-23T17:02:53.107

1

Can you try this and see if it works? If it doesn't can you let us know which line it errors on and what the error message says

Option Explicit
Public Sub Range_End()
    Dim X As Variant
    Dim Count As Long, FinalCol As Long
    Dim SRange As Range, XRange As Range, HideRange As Range

    With Sheets("Formatted")
        .Activate

        X = InputBox("enter row number")

        Set SRange = .Range(Cells(X, 3), Cells(X, .Cells(4, .Columns.Count).End(xlToRight).Column))
    End With

    Application.ScreenUpdating = False
    SRange.EntireColumn.Hidden = False

    For Each XRange In SRange
        If XRange.Value = vbNullString Then
            If HideRange Is Nothing Then
                Set HideRange = XRange
            Else
                Set HideRange = Union(HideRange, XRange)
            End If
        End If
    Next XRange

    If Not HideRange Is Nothing Then HideRange.EntireColumn.Hidden = True

    Application.ScreenUpdating = True
End Sub

edited May 23 '17 at 17:02

answered May 23 '17 at 16:56

Tom

9,725
3
31
48

what is "vnbnullstring"? it says that it is not defined – stackquestionfdsaf May 23 '17 at 16:58
1

is it vbnullstring? – stackquestionfdsaf May 23 '17 at 16:58
It was a typo - you're right. My apologies. Also I've just changed it a little bit more to remove your do loop as I don't think you need it (You're just using it to find the last column which can be done as I've done above instead) – Tom May 23 '17 at 16:59
I've just updated it again (can you try the latest version) This un-hides all columns in one hit, and then only hides the ones needed (instead of setting both properties in the loop) semantically it is the same as your code, however it's faster to do it in one go then doing each individually – Tom May 23 '17 at 17:03
No problem - don't forget to accept it as the answer (Hit the little tick next to the answer) – Tom May 23 '17 at 17:04

Excel vba: Method 'Range' of object '_Global' failed error in excel

1 Answers1