Help: Need refresher of some basic programming code

[Guest]

1. What's the code to find the last row in a Column (Field) that has data,
even if there are blank rows in-between?

2. Code to find last row on Sheet that has data, even if there are blank rows
in-between on Sheet?

Thank you

 

[Chip Pearson]

Have a look at the following code. It should get you going.

 

[Chip Pearson]

Forgot to paste the code.

Sub LastCell()
Dim R As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set R = WS.Cells.Find(what:="*", after:=WS.Cells(WS.Cells.Count))
If R Is Nothing Then
Debug.Print "R Is Nothing (Blank WS)"
Else
Debug.Print "Last Cell: " & R.Address
End If
End Sub

Sub LastRowOfColumn()
Dim R As Range
Dim WS As Worksheet
Const COL_LETTER = "F"
Set WS = ActiveSheet
With WS
Set R = .Cells(.Rows.Count, COL_LETTER).End(xlUp)
End With
If (R.Row = 1) And (R.Value = vbNullString) Then
Debug.Print "Blank column"
Else
Debug.Print "Last Used Row In Column " & COL_LETTER & ": " & R.Row
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Sub chris()
Dim n As Long
n = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox (n)
End Sub

for the last row with data in column A

 

[Guest]

For column A:

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For sheet:

sLastRow = ActiveSheet.UsedRange.Rows.Count

There are a few others.

 

[Leith Ross]

1. What's the code to find the last row in a Column (Field) that has data,
even if there are blank rows in-between?

2. Code to find last row on Sheet that has data, even if there are blank rows
in-between on Sheet?

Thank you

Hello Chris,

To find the last row of a column "A"...

With ActivveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

To find the last row with data on a sheet...

LastRow = ActiveSheet.UsedRange.Row

Sincerely,
Leith Ross

 

[Guest]

For your second question:

Sub ordinate()
Dim n As Long, m As Long
m = 0
For i = 1 To Columns.Count
n = Cells(Rows.Count, i).End(xlUp).Row
If n > m Then m = n
Next
MsgBox (m)
End Sub

unless you have a strong belief in the accuracy of ActiveSheet.UsedRange

 

[Chip Pearson]

Errata:

Set R = WS.Cells.Find(what:="*", after:=WS.Cells(WS.Cells.Count))

should be

Set R = WS.Cells.Find(what:="*", after:=WS.Cells(WS.Cells.Count),
searchdirection:=xlPrevious)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)