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

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


"Chirs" wrote in message
...
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

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)

"Chirs" wrote in message
...
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

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
--
Gary''s Student - gsnu200749


"Chirs" wrote:

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

For column A:

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

For sheet:

sLastRow = ActiveSheet.UsedRange.Rows.Count

There are a few others.

"Chirs" wrote:

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

On Oct 12, 10:25 am, Chirs wrote:
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

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
--
Gary''s Student - gsnu200749


"Chirs" wrote:

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

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)

"Chip Pearson" wrote in message
...
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)

"Chirs" wrote in message
...
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