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

 

[cmowla]

I edited the code in my previous post (see the code at the top). Sorry about that.

 

[kevin9999]

Another try

Sub Find_Last_Cell_3()
    Dim rng As Range, i As Long, j As Long
    i = 6
    Do While i < (10 * 3)   '<< second number is number of ranges
        Set rng = Cells(2, i).Resize(, 10)
        For j = 10 To 1 Step -1
            If rng.Cells(j) <> "" Then
                MsgBox "Last cell in this range is " & rng.Cells(j).Address
                Exit For
            End If
        Next j
        i = i + 10
    Loop
End Sub

 

[Akbarov]

Was about to gave up Thank you very much Kevin it works..

 

[kevin9999]

Was about to gave up Thank you very much Kevin it works..

I'm just glad we got there in the end. Thanks for the feedback

 

[cmowla]

@Akbarov

Hmm. On my test sheet, @kevin9999 's sub returns G2. Mine returns N2.

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, "$", "")

His is correct? How?

Book1.xlsb
	E	F	G	H	I	J	K	L	M	N	O	P
1		1
2			1
3				1
4					1
5						1
6							1
7								1
8									1
9										1
10											1
11
12
13
14
15
16					3
17
18							3
19
20
21
22
23
24
25			3
26
27
28
29
30										3
Sheet3

 

[Akbarov]

cmowla I guess you misunderstood question because of my loop. ​

Problem needed to find last non blank COLUMN in ROW 2.
in first loop F2:O2 --> result should be O2
Second loop P2:Y2 --> result should be Q2
Third loop Z2:AI2 --> result should be AA2 and so on..

 

[cmowla]

For what it's worth, should you not want to loop, here are two alternatives:

Sub Find_Last_Cell__UsingFormula()

Dim sh As Worksheet
Set sh = ActiveSheet

Dim x As Integer
x = 0
With Range(Cells(2, 6 + x), Cells(2, 6 + x + 9))
    MsgBox Split(Cells(1, Evaluate("MAX(IF(" & sh.Name & "!" & .Address & "<>" & Chr(34) & Chr(34) & ",COLUMN(" & sh.Name & "!" & .Address & "),0))")).Address, "$")(1) & 2
End With

End Sub

Sub Find_Last_Cell__UsingRangeFind()

Dim sh As Worksheet
Set sh = ActiveSheet

Dim x As Integer
x = 0
MsgBox sh.Cells(2, 6 + x).Resize(, x + 10).Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Address

End Sub

 

[cmowla]

And here's one more.

This assumes that if there are no non-blank cells, to return nothing (my other two probably return errors if this is not the case):

Sub Find_Last_Cell__CellBounds()

Dim sh As Worksheet
Set sh = ActiveSheet

Dim x As Integer
x = 0
With sh.Cells(2, x + 6 + 10).End(xlToLeft)
    If .column >= x + 6 Then MsgBox Split(Cells(1, .column).Address, "$")(1) & 2
End With

End Sub

But if there will always be at least one non-blank cell in the 1x10 block/range,

Sub Find_Last_Cell__CellBounds2()

Dim sh As Worksheet
Set sh = ActiveSheet

Dim x As Integer
x = 0
MsgBox Split(Cells(1, sh.Cells(2, x + 6 + 10).End(xlToLeft).column).Address, "$")(1) & 2

End Sub

 

[Akbarov]

Another try

Sub Find_Last_Cell_3()
    Dim rng As Range, i As Long, j As Long
    i = 6
    Do While i < (10 * 3)   '<< second number is number of ranges
        Set rng = Cells(2, i).Resize(, 10)
        For j = 10 To 1 Step -1
            If rng.Cells(j) <> "" Then
                MsgBox "Last cell in this range is " & rng.Cells(j).Address
                Exit For
            End If
        Next j
        i = i + 10
    Loop
End Sub

Kevin may be you can help me to put your code to my code? I got confused..

Sub UpdateChart() 'Excel VBA procedure to update the chart.
Dim CHARTDATA As Range
Dim i As Integer
Dim j As Long
Dim x As Long
Dim lw As Long
Dim lr As Long
Dim sh As Worksheet
Dim ws As Worksheet
Dim lc As Long
Dim lastCell As Range
Dim cell1 As Range

Set sh = ActiveSheet

x = 0

'Number of charts
For j = 1 To 54

'Find last non-blank series name
    Dim c
    For Each c In sh.Range(Cells(2, 6 + x), 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
 
'Finding last row
With ActiveSheet.Range("E3").CurrentRegion
     lr = .Rows(.Rows.Count).Row
 End With
lr = 55

'Set range of data
If j = 1 Then
Set CHARTDATA = sh.Range(Cells(3, 6 + x).Address, Cells(lr, 6 + lc).Address)
Else
Set CHARTDATA = sh.Range(Cells(3, 6 + x).Address, Cells(lr, lc - 1).Address)
End If

'Activate chart and make required changes
sh.ChartObjects("Cluster" & j).Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=CHARTDATA, PlotBy:=xlColumns
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.Orientation = 45
For i = 1 To ActiveChart.SeriesCollection.Count  'Headers to be added
Set cell1 = sh.Cells(2, 5 + i + x)

ActiveChart.SeriesCollection(i).Name = cell1.Value
Next i

ActiveChart.FullSeriesCollection(1).XValues = "='" & sh.Name & "'!" & "$E$3:$E$" & lr

If x > sh.ChartObjects.Count * 10 Then
Exit Sub
End If
x = x + 10
i = 0
Next j

End Sub

"Find last non-blank series name" need to be replaced with your code. and "lc" need to be your code result: rng.Cells(j).Column