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 ?

 

[StephenCrump]

And you're saying this doesn't change if you select CO37 and click the Delete key?

 

[Alex Blakenburg]

What do you get if in a blank cell somewhere on the sheet you enter this:

=UNICODE(CO37)

 

[AlexanderBB]

Stephen - Yes.
Alex, Typed that into CX37. Clicked away and it changed to #NAME?.

 

[StephenCrump]

Can you post the dodgy data into a small workbook (making sure there's no sensitive or confidential information), upload to a dropbox site and post the link so we can take a look?

 

[Alex Blakenburg]

A copy as suggested by Stephen would be ideal. In the meantime try this in the immediate window including the question mark and hit enter (Ctrl+G if you can't see the immediate window) and tell us what it returns.

? ascw(Range("CO37").value)

 

[AlexanderBB]

Alex, it tells me
Run-time error '5':
Invalid procedure call or argument
Same thing after changing ascw to asc
? Range("CO37").value prints nothing.
Yes, giving you the worksheet wold make it much easier. I don't have Dropbox but may be able to organise something. I'll try.
May take 30 mins.

 

[AlexanderBB]

Stephen, Alex

http://www.britburn.co.uk/files/spreadsheet/ss2024.xlsm

 

[Alex Blakenburg]

Why do you have the week columns that are intended to hold a number formatted as Text ?

 

[AlexanderBB]

Because some value there (pairs) include "=". Poss on other sheets. Complete thing has 5.

 

[Alex Blakenburg]

Can you show us an example. Include the formula bar in what you show us.