last row/col question...

[Cummins]

Regarding the identification of the last row or column of a spreadsheet, is it dangerous to use the single line below instead of the lengthy function lastrowN below it?
My mind prefers simplicity but if it creates problems....


lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row


Function lastrowN()
' Returns last row1 in activesheet
Dim ExcelLastCell As Range
Dim row1 As Double, row2 As Double, rowmove As Double

' ExcelLastCell is what Excel thinks is the last cell
Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
' Determine the last row1 with data in it
row1 = ExcelLastCell.Row
rowmove = Int(row1 / 2 - 1) + 1
row2 = row1 - rowmove
Do While Application.CountA(Range(Cells(row1, 1), Cells(row1, 256))) = 0 _
And row1 <> 1
'Application.StatusBar = Row1
If Application.CountA(Range(Cells(row1, 1), Cells(row2, 256))) = 0 Then
rowmove = Int(rowmove / 2 - 1) + 1
row1 = row2
If rowmove < 1 Then rowmove = 1
row2 = row2 - rowmove
If row2 < 1 Then row2 = 1
Else
rowmove = Int(rowmove / 2 - 1) + 1
If rowmove < 1 Then rowmove = 1
row2 = row1 - rowmove
row1 = row1 - 1
End If
Loop
lastrowN = row1
End Function

 

[kpark91]

Do not use SpecialCells to get Last Cell.

Just in order to get last row or column try something like

Range("A" & Rows.count).End(xlUP).Row
Cells(1, Columns.count).End(xlToLeft).Column

End(xlUp) or End(xlToLeft) simulate the thing as when you press ctrl+up or left in excel spreadsheet.

 

[Misca]

The short one is a bit dangerous because it will return the last modified cell address: Try coloring the very last cell in your worksheet and see what it returns. And see what do you get when you delete the colored cell. Then try saving the workbook and see what you get.

Usually it's not the last cell of the sheet that you're really interested in but the last cell in a certain range or column:

Dim LastRow As Long

'This returns the last row of the A1 current region.
'If the range doesn't start from row 1 you need to add the rows before
LastRow = Range("A1").CurrentRegion.Rows.Count

'This returns the last row in column B with a value on it:
LastRow = Range("B" & Rows.Count).End(xlUp).Row

 

[Cummins]

Thanks...I like it. What issues are there with specialcells?

 

[Cummins]

Thanks Misca...didn't see your post before my reply.
That was the danger I was concerned about. This function may be working for me now but I want to know what pitfalls exist. I'll swap!

 

[Cummins]

Another question, in both of your suggestions, it requires that there is data in that column. If there is missing data in that particular column compared to the others, will it recognize it? If I try doing control/shift, arrow down on an actual sheet, it stops when it sees missing data even if there is more data beyond it.

 

[kpark91]

Hi, Cummins.

I'm not too sure what you mean by recognizing a missing data.

The code posted will return you the last row or column in a specific column or row.
So, despite the missing data, it will return the last row and column.
Now it is up to you to implement this and use it to ur specific situation.

and I think there is a misunderstanding of the code so I will tell you why/how it works.
Range("A" & Rows.count) = last cell of the spreadsheet in a column (A66536 or w.e)
.End(xlUp) = same as Ctrl+Up in the spreadsheet from the last cell, selects the last filled cell.
.Row = returns the row number