FINDING LAST ROW IN COLUMN NOT WORKING

[DeanP]

I am trying to find the last row with data in a single column. The result should show row 59, however it shows row 1. I have one blank row in my column, which is row 2.

My code:

Sub LastRow1()
Dim sht1 As Worksheet
Dim Lr1 As Long
Dim rng1 As Range

Set sht1 = Worksheets("Contents")

With sht1
Lr1 = Cells(Rows.Count, "C").End(xlUp).Row

MsgBox Lr1

Set rng1 = sht1.Range("C3:C" & Lr1)
End With
End sub

The message box answer = 1.

I can't see what is wrong?

[Rob342]

Hi Dean
Try this



Lr1 = .Range("C" & Rows.Count).End(xlUp).Row

[mancubus]

Sub LastRow1()
Dim rng1 As Range

With Worksheets("Contents")
Set rng1 = .Range("C3:C" & .Cells(.Rows.Count, 3).End(xlup).Row)
End With
End Sub

[yujin]

You need to add a dot before Cells property.


Lr1 = .Cells(Rows.Count, "C").End(xlUp).Row

[DeanP]

Thank you all!