Range Question

[AlexanderBB]

Someone gave me this line of code

Set xx = Range(Range("Y" & therow), Range("CQ" & therow).End(xlToLeft))

If I debug.print xx.address what should I expect to see ?

 

[StephenCrump]

You've posted several threads with variations on this question.

So more to the point, what do you see in your circumstances? And if that's a problem, what do you expect to see, and why?

 

[Alex Blakenburg]

For a visual on what you can expect.
• Select CQ in the row that is concerning you
• Pressl Ctrl+<left arrow>
• This will take you to the first non-empty cell.
Note: If Y is also empty it will take you past Y

Whatever cell that lands on call if foundCell will be used as the end cell in the range starting at Y
ie
Range( Y_TheRow : foundCell )
there is nothing to stop foundCell to be to the left of column Y if all those cells are empty.
So say its in row 2 if there is data in CP then Y2:CP2, if the row is empty or only has data in column A then Y2:A2.

 

[AlexanderBB]

I have indeed Stephen and still trying to nail it down and see why I can't rely on it. If I can't rely on it I can't deploy it.
That example was useful Alex and gives the same wrong result CF. Should be AQ.
So Excel thinks something is in CF. Whatever it is, it's invisible thus NOT the result wanted. Stephen gave me a pretty good fix for but it's only needed on some rows.
You don't know which so have to run it on all/any.
This gives the 'wanted' result

Function lastPopCol(iRow As Long, CR As String, sht As String) As Integer
Stop
    'CR is "Y-CP"
    Dim i As Integer
    Dim e As Integer
    Dim s As Integer
    With Worksheets(sht)
        e = ColNumber(CStr(Split(CR, "-")(1)), .Name) '=94
        s = ColNumber(CStr(Split(CR, "-")(0)), .Name) '= 25
        For i = e To s Step -1
            If Val(.Cells(iRow, i)) > 0 Then
                lastPopCol = i '= 43
                Exit Function
            End If
        Next
    End With
End Function

 

[Trebor76]

I'm not sure I fully understand but have a look at this where I've added notes on the how xx range is being set (needs to run on the sheet you want to set the range for):

Option Explicit
Sub Macro1()

    Dim therow As Long, thecolumn As Long
    Dim xx As Range
   
    therow = 5
    thecolumn = Cells(therow, Columns.Count).End(xlToLeft).Column
   
    'If the last column in the 'therow' row is greater than or equal to 26 (Col. Z) i.e. one more column from Y then...
    If thecolumn >= 26 Then
        '...set the range 'xx' from Col. Y to the last column
        Set xx = Range(Cells(therow, 25), Cells(therow, thecolumn))
    'Else...
    Else
        '...set the range 'xx' just on Col. Y of the 'therow' row
        Set xx = Cells(therow, 25)
    End If
   
    Debug.Print xx.Address

End Sub

Hope that helps.

Regards,

Robert

 

[AlexanderBB]

Hi Trebor,
That's sets a range to $Y$1180:$CU$1180 which is correct expect it needs to stop at CP. There's data at CQ-CU which isn't wanted.
It's the last cell in Y-CP only. But as Alex demo's Excel finds cells that aren't showing anything but contain something.
My clunky code in msg 4 seems to do it. Or Stephens fix is ok too.

With Intersect(UsedRange, Range("Y" & erow & ":CP" & erow))
            'xxx is something that won't be found in the range.  Change if necessary!
            .Replace What:="", Replacement:="xxx", LookAt:=xlWhole, MatchCase:=False
            .Replace What:="xxx", Replacement:="", LookAt:=xlWhole, MatchCase:=False
        End With

Then I was setting the range again, and confirming it..
I think a solution doesn't exist, apart frpm the above.

 

[Trebor76]

Excel finds cells that aren't showing anything but contain something

Are they the result of NULL formulas like this:

=IF(1+1=2,"","No it doesn't")

If so maybe this:

Option Explicit
Sub Macro1()

    Dim therow As Long, thecolumn As Long
    Dim xx As Range
   
    therow = 5
    thecolumn = Range("Y" & therow & ":CP" & therow).Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlValues).Column
       
    'If the last column in the 'therow' row is greater than or equal to 26 (Col. Z) i.e. one more column from Y then...
    If thecolumn >= 26 Then
        '...set the range 'xx' from Col. Y to the last column
        Set xx = Range(Cells(therow, 25), Cells(therow, thecolumn))
    'Else...
    Else
        '...set the range 'xx' just on Col. Y of the 'therow' row
        Set xx = Cells(therow, 25)
    End If
   
    Debug.Print xx.Address

End Sub

If not I'm out of ideas I'm afraid

 

[StephenCrump]

I'm not sure why you're still stuck on this?

The problem you have is zero length strings, which presumably come from some external data source.

The code in Post #6 fixes the issue for that particular range.

If you expand that range to include all cells potentially affected (as in Post #51 here: Last Col in Range), then you can fix your data in one go. Then the code snippet in Post #1 should return the desired result, for all values of therow.

 

[AlexanderBB]

I did consider using the fix to remove them all, but I'd still need to test for the problem because whatever is adding them may do so again.
I'm not so much stuck, just find it hard to believe Excel won't return a range of cells with valid values (without processing them in some way first)
I can rely on the msg4 code even if it's not very elegant.
But it's been done to death now and I do appreciate all the help and ideas. Particularly your tip not needing to loop through a range. Cheers