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 ?

 

[Fuji]

Need to see your actual data, but one more try
add one line like

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

 

[StephenCrump]

Test first that it is a null string problem in your case. If you do this in VBA ...

Range("CP37").Value = Range("CP37").Value

Set IRange = Range(Range("Y37"), Range("CQ37").End(xlToLeft))

... is IRange now correct?

If so, then can you remove null strings for the required data range as follows:

With Range("SomeRange")
    .Value = .Value
End With

 

[AlexanderBB]

Fuji I wasn't sure how to define "cells" in the first row.
Stephen, Yes this now evaluates correctly for row 37

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

Your with Range code to remove null strings - can "SomeRange" be more than one column?
I imagine any column with this problem (like CP) will similarly cause the above to not return what's wanted.

 

[StephenCrump]

can "SomeRange" be more than one column?

Range("SomeRange") just indicates some range object you define according to what you need.

This might be, for example:

Worksheets("Sheet1").UsedRange

 

[AlexanderBB]

So this works (there's no error)?

            Set IRange = Range("AI36:CP36")
            With IRange
            .Value = .Value
            End With

I wondered if For each cell in... would be needed.

 

[Fuji]

Fuji I wasn't sure how to define "cells" in the first row.

Why you think you need to define cells?
cells is the whole range in worksheet, no need to define.

 

[AlexanderBB]

I didn't know that Fuji.
The Msgbox prints
$Y$37:$CU$37

 

[AlexanderBB]

Apologies Fuji I did know about cells. This thing has sent me into such a state I've lost the plot.
Maybe one last attempt to fix. Otherwise should I stop using in this Range business and use the loop below ?
Column CP that was the culprit is now Ok and the problem moved to Column CO.
To 'fix' CP I clicked it and pressed Delete. This doesn't work in CO. Not does the .value-.value method from Stephen.
This is what I get now

 Set IRange = Range(Range("Y" & eRow), Range("CQ" & eRow).End(xlToLeft))
            If eRow = 37 Then
                Debug.Print IRange.address
                For w = 94 To 25 Step -1 'CP-Y
                    If Cells(37, w) > "" Then
                        Debug.Print "Last Col is " & w; " "; colLetter(w)
                        Exit For
                    End If
                Next w
                Range("A10413").Formula = "="""""
                Range("A10413").Copy
                With Range("CO37")
                    .PasteSpecial xlPasteValues
                    Debug.Print "Is CO37 empty? " & Evaluate("isblank(B1)")
                    .Value = .Value
                    Debug.Print "Now is CO37 empty? " & Evaluate("isblank(B1)")
                End With
                Set IRange = Range(Range("Y" & eRow), Range("CQ" & eRow).End(xlToLeft))
                Debug.Print IRange.address
                Debug.Print IIf(Cells(37, "CO") = "", "Empty", "Not empty")
            Stop
            End If

And the Debug windows shows
$Y$37:$CO$37
Last Col is 39 AM
Is CO37 empty? True
Now is CO37 empty? True
$Y$37:$CO$37
Empty

Irange.address ideally should be $Y$37:$AM$37.

 

[StephenCrump]

What happens if you run this?

Sub Test()

    Dim IRange  As Range, LastCell As Range
    Dim eRow As Long
    
    eRow = 37
    
    With Range("Y" & eRow & ":CQ" & eRow)
        .Value = .Value
    End With
        
    Set LastCell = Range("CQ" & eRow).End(xlToLeft)
    Set IRange = Range(Range("Y" & eRow), LastCell)
    MsgBox "Address is: " & IRange.Address & vbLf & "Last cell value " & LastCell.Value & vbLf _
        & "Len(Last Cell) " & Len(LastCell) & vbLf & "Is last cell empty? " & Evaluate("isblank(" & LastCell.Address & ")")
                    
End Sub

 

[AlexanderBB]

Thanks for persevering with this Stephen. Can't believe it's like this!

Msgbox prints
Address is: $Y$37:$CO$37
Last cell value
Len(Last Cell) 0
Is last cell empty? False