Find last non-blank cell in range

[Akbarov]

Hello community,

I am trying to find last non-blank cell in F2:O2
I tried this way. But it finds first

    Dim c
    For Each c In sh.Range(Cells(2, 6 + x).Address, Cells(2, 6 + x).Offset(0, 9))
        If c = "" Then
         lc = c.Column
            Exit For
        End If
    Next
 If lc = 0 Then lc = 9 + x

For example in following example I need to set range from Younger to Older. But my code stops at middle age

YOUNGER

MIDDLE AGE

OLDER

 

[Leo Skywalker]

Unless you really need VBA for that, if the cells contain text, then this will return the column number of the last non blank cell:

=COLUMN(F2)+MATCH("",F2:O2,-1)-1

 

[kevin9999]

Not sure what you want to do with it once you find it, but...

Sub Find_Last_Cell()
    Dim c As Range
    Set c = ActiveSheet.Range("F2:O2").SpecialCells(xlCellTypeLastCell)
    MsgBox c.Address
End Sub

 

[Akbarov]

Your code gave me some strange result $AOZ$2057

 

[kevin9999]

Your code gave me some strange result $AOZ$2057

That would have been the last cell where there had been some data at some point in time. Is there anything in that cell now? A formula or a space? If not, save your file & run it again.

 

[Akbarov]

I guess I miss explained my situation.

as you can see in my code, it is loop, it needs to find last non-blank cell within F2:O2 then next loop will be P2:Y2 and so on..

 

[cmowla]

Here's an alternative:

Dim c As Integer, lastRow As Integer
lastRow = 0
For c = Range("F1").Column To Range("O1").Column
    If lastRow < sh.Cells(Rows.Count, c).End(xlUp).Row Then lastRow = sh.Cells(Rows.Count, c).End(xlUp).Row
Next c
MsgBox lastRow

 

[Akbarov]

Thanks for reply, but I don't need last row. I need last non-blank within F2:O2
So result should be something like J2

 

[cmowla]

How about this?

Dim c As Integer, lastRow As Integer, column As Integer
lastRow = 0
x = 0
column = 1
For c = Cells(1, 6 + x).column To Cells(1, 6 + x + 9).column
    If lastRow < sh.Cells(Rows.Count, c).End(xlUp).Row Then
        lastRow = sh.Cells(Rows.Count, c).End(xlUp).Row
        column = c
    End If
Next c
MsgBox Split(Cells(1, column).Address, "$")(1) & lastRow

 

[kevin9999]

Understood now

Try this

Sub Find_Last_Cell_2()
    Dim rng As Range, i As Long, LCol As Long
    i = 6
    Do While i < (10 * 3) '<<< change 2nd number to match the number of loops you want to do
        Set rng = Cells(2, i).Resize(, 10)
        On Error GoTo skip
        LCol = rng.Find("*", , xlFormulas, , 2, 2).Column
        MsgBox "Last cell in this range is " & Cells(2, LCol).Address
        i = i + 10
    Loop
skip:
End Sub