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

 

[Akbarov]

I made some changes on cmowla's code, now result is correct.

Dim sh As Worksheet
Set sh = ActiveSheet

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(Columns.Count, c).End(xlUp).column Then
        lastRow = sh.Cells(Columns.Count, c).End(xlUp).column
        column = c
    End If
Next c
MsgBox Split(Cells(1, column).Address, "$")(1) & 2

Kevin thanks for reply , will try and feedback your code.

 

[Akbarov]

Kevin your code shows always 10th column. May be it is because cells are not empty ? there are formulas

 

[cmowla]

If you don't mind using Range.Find, then you can do this without looping:

Dim x As Integer
x = 0
Dim searchRange As Range, lastNonBlank As Range
Set searchRange = sh.Cells(2, x + 6).Resize(Rows.Count - 1, x + 10)
Set lastNonBlank = sh.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas)
MsgBox Replace(lastNonBlank.Address, "$", "")

 

[Akbarov]

Not works.. I don't need row.count. Row number is 2 Fixed.

 

[cmowla]

Not works.. I don't need row.count. Row number is 2 Fixed.

Rows.Count = 1048576, not 2. I have 2 hardcoded in sh.Cells(2, x + 6).

To get it to work, you will have to tweak it like you did my previous.

 

[kevin9999]

Kevin your code shows always 10th column. May be it is because cells are not empty ? there are formulas

Yes, formulas would explain it.

 

[Akbarov]

May be this image will explain my goal better ?
I need to get marked ranges on each loop

 

[jolivanes]

Maybe show your formulas.

 

[cmowla]

Looking at how you tweaked my last code, it appears you want to find the last non-blank. But after it's found, you want to only return the column of that non-blank at row 2. (Not the actual non-blank cell, as what my previous code does.) This tweak to my previous code gives this result:

Dim x As Integer
x = 0
Dim searchRange As Range, lastNonBlank As Range
Set searchRange = sh.Cells(2, x + 6).Resize(Rows.Count - 1, x + 10)
Set lastNonBlank = searchRange.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas)
MsgBox Replace(Cells(2, lastNonBlank.column).Address, "$", "")

EDIT:
And I noticed that with my previous code, it was incorrect for finding the last non-blank cell. Here's the fix (for those interested):

Dim x As Integer
x = 0
Dim searchRange As Range, lastNonBlank As Range
Set searchRange = sh.Cells(2, x + 6).Resize(Rows.Count - 1, x + 10)
Set lastNonBlank = searchRange.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas)
MsgBox Replace(lastNonBlank.Address, "$", "")

(I made the same mistake in my first edit of this post, so @Akbarov please see the edited version above.)

 

[Akbarov]

Yes as I mentioned before your code worked fine for first loop. But not for next loops.
Here what I tried now, and it always return F2. When I changed rows.count to columns now it return last column in whole worksheet.

Sub test()

Dim sh As Worksheet
Set sh = ActiveSheet

Dim x As Integer
For x = 0 To 30 Step 10
Dim searchRange As Range, lastNonBlank As Range
Set searchRange = sh.Cells(2, x + 6).Resize(2, x + 10)
Set lastNonBlank = sh.Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas)

MsgBox Replace(sh.Cells(2, lastNonBlank.column).Address, "$", "")
Next x
End Sub