Last Col in Range

[AlexanderBB]

I was given this for a range from Y to the last used Col from CP in Row eRow,

Set iRange = Range(Range("Y" & eRow), Range("CQ" & eRow).End(xlToLeft))

And it's worked many times fine, until suddenly iRange.address is $Y37:$CP$37
So I assumed there was a rogue invisible character somewhere and tried this

                    kcol = 94
                    Do Until Val(Cells(eRow, kcol)) > 0
                    Cells(eRow, kcol) = ""
                    kcol = kcol - 1
                    Loop
                    Set iRange = Range(Range("Y" & eRow), Range("CQ" & eRow).End(xlToLeft))

Thinking that would fix it. But iRange address is still $Y37:$CP$37
What might cause this ?

 

[Skyybot]

What is iRange address supposed to be?

 

[AlexanderBB]

It varies. Y to the last populated column before CQ, in any row.

 

[Skyybot]

Entire Columns?

 

[StephenCrump]

So I assumed there was a rogue invisible character somewhere and tried this ...

The code you've posted won't fix this.

Why don't you look directly at what's in CP37, e.g. using the Excel LEN() and CODE() functions?

 

[AlexanderBB]

Len(Cells(37, "CP") is 0. Not sure how to use CODE()

 

[Skyybot]

Maybe this?

Set iRange = Range(Range("Y" & eRow), Range(Cells(eRow, Me.UsedRange.Columns.Count)) 'Replace Me with Sheet variable if needed

 

[StephenCrump]

If you look at the formula bar, what's in CP37?

 

[AlexanderBB]

Skybot, the debug window says 'Expected list separator or )'
Me.Me.UsedRange.Columns.Count is 100. The correct last col in the row.

 

[AlexanderBB]

Formula bar shows nothing