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
– A.S.H, Commented May 23, 2017 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
– BruceWayne, Commented May 23, 2017 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
– Tim Williams, Commented May 23, 2017 at 16:36
@A.S.H Could you help me edit the code? i tried your method, it doesn't work:( — stackquestionfdsaf
– stackquestionfdsaf, Commented May 23, 2017 at 16:50
@TimWilliams I made some changes but it doesn't seem to make any improvements:( — stackquestionfdsaf
– stackquestionfdsaf, Commented May 23, 2017 at 16:50

Tom · Accepted Answer · 2017-05-23 17:02:53Z

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, 2017 at 17:02

answered May 23, 2017 at 16:56

Tom

9,9084 gold badges33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

stackquestionfdsaf Over a year ago

what is "vnbnullstring"? it says that it is not defined

stackquestionfdsaf Over a year ago

is it vbnullstring?

Tom Over a year ago

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 Over a year ago

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 Over a year ago

No problem - don't forget to accept it as the answer (Hit the little tick next to the answer)

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related