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]

Add one more ) at the end

 

[Skyybot]

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

 

[AlexanderBB]

This seems to sort it, but be better to find the actual problem.

         Set iRange = Range(Range("Y" & eRow), Range("CQ" & eRow).End(xlToLeft))
                RightUsedCol = iRange.Columns(iRange.Columns.Count).Column
                If Val(Cells(eRow, RightUsedCol)) = 0 Then 'Range not correct. Last Col must be > 0
                    kcol = 94
                    Do Until Val(Cells(eRow, kcol)) > 0
                        Cells(eRow, kcol) = ""
                        kcol = kcol - 1
                    Loop
                    Set iRange = Range("Y" & eRow & ":" & colLetter(kcol) & eRow)
                End If

 

[StephenCrump]

What happens if you run this code?

Sub Test()

    Dim IRange  As Range
    Dim eRow As Long
    
    eRow = 3
    
    Set IRange = Range(Range("Y" & eRow), Range("CQ" & eRow).End(xlToLeft))
    MsgBox "Address is: " & IRange.Address
        
End Sub

 

[AlexanderBB]

Skybot
Run-time error '1004':
Application-defined or object-defined error

 

[AlexanderBB]

Stephen
Address is: $Y$3:$AM$3

and with row 37
Address is: $Y$37:$CP$37

 

[AlexanderBB]

Have only just been shown these Range/Find methods and thought they'd ideal but they seem very flacky and only work sometimes.
This fails too

Set yy = .Range("Y" & y.Row & ":CP" & y.Row).Find(what:=Hi, lookIn:=xlValues, LookAt:=xlWhole)

y,row is 60. Y60 Contains 4. Z60 is also 4. Hi is 4.
yy.address should be $Y$60 but is $Z$60.
Both cells are text so it's a not a datatype issue. And the above code worked correctly for rows 2-59.

 

[jolivanes]

Don't know what this means. "from Y to the last used Col from CP in Row eRow"

If eRow = 10, this will give you the last used Column in eRow.

Sub Last_Column()
Dim eRow As Long
eRow = 10
MsgBox Range("XFD" & eRow).End(xlToLeft).Column
End Sub

Maybe I missed it but could you explain in a concise manner what you want to achieve.

 

[jolivanes]

If the last column cannot be less then the Y column ( = 25), maybe like so

Sub Last_Column_A()
Dim eRow As Long, lc As Long
eRow = 10    '<---- Change as required
lc = IIf(Range("XFD" & eRow).End(xlToLeft).Column < 25, 25, Range("XFD" & eRow).End(xlToLeft).Column)
MsgBox lc
End Sub

 

[AlexanderBB]

It's the last used Column in range "Y:CP" The row is every row being iterated.
The code I have (previous msgs) mostly works, but I have to write my own clunky check to verify it.
Kind of odd.