Cannot jump to Range because it is hidden

Apologies if this has been answered before, but I searched multiple forums and did not find a response that was adequate and/or solved my current problem.

I am in the process of trying to code a Macro for converting text to numbers on 2 separate worksheets (see example below)

 Sheets("SDP LW").Select
    Cells.AutoFilter
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
    Selection.NumberFormat = "General"
    .Value = .Value
    End With
    
    Sheets("SDP YTD").Select
    Cells.AutoFilter
    Range("A3").Select
    Range(Selection, Selection.End(x1Down)).Select 
    With Selection
    Selection.NumberFormat = "General"
    .Value = .Value
    End With

The first part goes smoothly and for the second part, I get a debug pop up and the "Range(Selection, Selection.End(x1Down)).Select" portion of the code is highlighted. When I right click on the highlighted portion, I get the message "Cannot jump to range because it is hidden".

Any ideas for updating the code so it runs would be greatly appreciated.

Hi katie,

Welcome to the Forum!

Try:

Sub quailkatie()
Sheets("SDP LW").Range("A:A").NumberFormat = "General"
Sheets("SDP YTD").Range("A:A").NumberFormat = "General"
End Sub

Hi Katie,

It baffles me

Can you:Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

Hi Katie,

You had leading apostrophes in all those fields - try this:

Sub quailkatie(): Dim ww As Worksheet, wy As Worksheet, ws As Worksheet
Dim n As Long, Cols: Cols = Array("A", "C", "D", "E")
Set ww = Sheets("SDP LW"): Set wy = Sheets("SDP YTD")
Set ws = ww: GoSub NumIt:
Set ws = wy: GoSub NumIt
            Exit Sub
NumIt:
With ws
For n = LBound(Cols) To UBound(Cols)
Columns(Cols(n) & ":" & Cols(n)).TextToColumns Destination:=Range(Cols(n) & "1"), _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="'", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        Next n
End With: Return
End Sub

I have often noted that SAP is well-named

What routines should I try?

The one I sent you gets rid of those apostrophe's and formats the column general.

Hi Katie,

Do you run my code at the outset? My sample has no code, no routines to try

Its not pretty, but I got it to work for my needs.

Sheets("SDP YTD").Select
Columns(("A") & ":" & ("A")).TextToColumns Destination:=Range(("A") & "1"), _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="'", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
  Columns(("E") & ":" & ("E")).TextToColumns Destination:=Range(("E") & "1"), _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="'", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        
        Sheets("SDP LW").Select
        Columns(("A") & ":" & ("A")).TextToColumns Destination:=Range(("A") & "1"), _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="'", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
  Columns(("E") & ":" & ("E")).TextToColumns Destination:=Range(("E") & "1"), _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="'", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

Thanks again!

Hi Katie,

Thanks for the rep! Glad you got it working!

BTW - Your code tags are done incorrectly - just highlight your code and click on the #.