Range with no values

[AlexanderBB]

I can't find the exact thread here where I was given

Set cr = .Range(.Range("Y" & Row), .Range("CQ" & Row).End(xlToLeft))

This catches all values in row from Y to CP, ignoring blanks. I've been using it and getting perfect results until a situation
where nothing was in that row. Say it's row 53, I get
$X$53:$Y$53
The X column is wrong. And I'm unsure how best to correct, and still rely on that instruction.

 

[Alex Blakenburg]

See if this give you some ideas.

    Set sht = ActiveSheet
    
    Dim LastCol As Long
    With sht
        LastCol = .Range("CQ" & Row).End(xlToLeft).Column
        If LastCol >= Columns("Y").Column Then
            Set cr = .Range(.Range("Y" & Row), .Range("CQ" & Row).End(xlToLeft))
            ' OR you could use
            ' Set cr = .Range(.Range("Y" & Row), .Cells(Row, LastCol))
        Else
            ' What do you want it to do if there is nothing there
            MsgBox "No data in Row for nominated range"
        End If
    End With

 

[AlexanderBB]

That's very helpful and has given me a solution. LastCol is really all I need.
It would be better as chars, so I'm using = Left(Cells(1, Val(intNumber)).Address(0, 0), 2 + (Val(intNumber) <= 26))
Where intNumber is your LastCol.
Unless there's something better?

 

[Alex Blakenburg]

This is probably cleaner:

Split(Cells(1, intNumber).Address, "$")(1)

 

[AlexanderBB]

Thanks Alex, Split was my first thought but now I have

 LastCol = (.Range("CQ" & index).End(xlToLeft).Column)
        If LastCol > 24 Then
            For Each ITEM In .Range("Y" & index, colLetter(LastCol) & index)
                Debug.Print ITEM & Space(1);
            Next
        End If

and don't need to define a range.
ColLetter changes a number to letters. I don't know how to use integers in .Range()

 

[Alex Blakenburg]

 .Range("Y" & index, .Cells(index,LastCol))

 

[AlexanderBB]

That did it. Great result Thanks. More to Range than I knew.