Find last row with data

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,225
Office Version
  1. 2016
Platform
  1. Windows
Need to find the last row in column C, but find the last filled cell and not just the last row.

In column C there is a formula down to row 7, but based on the formula in column C there are only results down to row 3. With the macro below the Msgbox returns 7 but need to return 3.

Code:
Sub findlast()
    Dim lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    MsgBox lr
End Sub
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,903
Office Version
  1. 365
Platform
  1. Windows
Need to find the last row in column C, but find the last filled cell and not just the last row.

In column C there is a formula down to row 7, but based on the formula in column C there are only results down to row 3. With the macro below the Msgbox returns 7 but need to return 3.

Code:
Sub findlast()
    Dim lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    MsgBox lr
End Sub

Technically what happens is correct, if cells C4 to C7 hold a formula that shows a blank then they are filled with the formula.

You can get the result you want with

Code:
Sub findlast()
    Dim lr As Long
    lr = Application.WorksheetFunction.CountIf([C:C], "<=>")
    MsgBox "C" & lr
End Sub

But that will not work if, for example C1 and C3 hold values, but C2 in blank.
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,225
Office Version
  1. 2016
Platform
  1. Windows
Thanks Jason that makes sense and it gets me further down the road :biggrin:
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,903
Office Version
  1. 365
Platform
  1. Windows
Try

Code:
Sub findlast()
    Dim lr As Long
        For lr = Range("C" & Rows.Count).End(xlUp).Row To 1 Step -1
            If Len(Range("C" & lr)) > 0 Then Exit For
        Next
    MsgBox "C" & lr
End Sub

Think that should overcome any problems.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,388
Messages
5,595,889
Members
414,029
Latest member
mrwilker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top