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 ?

 

[AlexanderBB]

Jolivanes, I'll try that but what is "XFD" ?

 

[jolivanes]

XFD is the last Column (16,384) in 2007 and up.

BTW, I like your "mostly works" in Post #20. Nothing like having a happy camper.

 

[AlexanderBB]

I'd be even happier if it always worked !
lc = IIf(Range("XFD" & eRow).End(xlToLeft).Column < 25, 25, Range("XFD" & eRow).End(xlToLeft).Column)

LC = 99. That's the last used column in the row ="CU"
But it's greater than "CP" so not withing the range to test.
One good thing , the bug/error/problem is consistent.

 

[StephenCrump]

What does this code do?

Sub Test()

    Dim IRange  As Range
    Dim eRow As Long
    
    'Sorry, my typo previously, meant to say 37
    eRow = 37
    
    Set IRange = Range(Range("Y" & eRow), Range("CQ" & eRow).End(xlToLeft))
    MsgBox "Address is: " & IRange.Address & vbLf & "Is CP37 blank? " & Evaluate("isblank(cp37)")
                
End Sub

 

[AlexanderBB]

Address is: $Y$37:$CP$37
Is CP37 blank? False

Is that means CP37 is not blank, then this is bit suspect ? Yes ?

? irange.address
$Y$37:$CP$37
? len(worksheets("Sheet2").Cells(37, "CP"))
0

Stopped after Msgbox.... in your Sub Test

 

[StephenCrump]

Is that means CP37 is not blank, then this is bit suspect ? Yes ?

Yes, it explains why in your original post, iRange.address is $Y37:$CP$37

Given LEN(CP37) = 0, the two obvious possibilities are a null string "" (either entered directly, or as the result of a formula) or a single quotation mark ' But these would show up in the formula bar, which you ruled out in Post #10.

The quick solution is to use the delete key, to delete the contents of the cell. But this won't help if this is a recurring, rather than one-off, problem. Are you getting your data from a feed or some external source?

 

[Fuji]

See if this works,

    eRow = 37
    kcol = Evaluate(Replace("max(if(trim(clean(a#:xfd#))<>"""",column(a:xfd)))", "#", eRow))
    MsgBox Range("y" & eRow, Cells(eRow, kcol)).Address

 

[StephenCrump]

But these would show up in the formula bar ...

On further thought, they might not appear in the formula bar. Let's make B1 the problem cell, by putting in a null string ....

Sub Test()

    Range("A1").Formula = "="""""
    Range("A1").Copy
    With Range("B1")
        .PasteSpecial xlPasteValues
        MsgBox "Is B1 empty? " & Evaluate("isblank(B1)")
        .Value = .Value
        MsgBox "Now is B1 empty? " & Evaluate("isblank(B1)")
    End With
    
End Sub

 

[AlexanderBB]

Fuji
$Y$37:$CU$37
Stephen, after DEL on CP37 Your Test shows
Address is: $Y$37:$CO$37
Is CP37 blank? True
And in B1 test it was first
Is B1 empty? False
then
Now is B1 empty? True

So thank you for nailing this. Is there a fix to handle that so
= Range(Range("Y" & eRow), Range("CQ" & eRow).End(xlToLeft))
Will ignore these invisible chars?
The data was mostly hand typed or pasted in. But it's now connected to Access for edits/updates.

 

[AlexanderBB]

Y-CP has valid numbers, anything else is irrelevant.
Maybe I could Null the whole range, except for valid data ?