I have a table which ends at row 5000. Using the following line in a macro
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Select
goes to row 5000 each time even thought the last row with data is much
further up. Is Excel interpreting the last row of a table as containing
data? This should be simple but I'm stumped as to why it is doing this.

If you have a formula in those cells, you will always end up at row 5000 no matter how the cell
appears.

But if the formula returns either a number or "" then you could use something like

Sub Macro1()
Dim myR As Range
Dim myC As Range

Set myR = Range("A2:A5000").SpecialCells(xlCellTypeFormulas, 1)
Set myC = myR.Areas(myR.Areas.Count).Cells(myR.Areas(myR.Are as.Count).Cells.Count)
myC.Select
MsgBox "The last cell with a number is in row " & myC.Row
End Sub


HTH,
Bernie
MS Excel MVP


"CoolBusiness" wrote in message
...
I have a table which ends at row 5000. Using the following line in a macro
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Select
goes to row 5000 each time even thought the last row with data is much
further up. Is Excel interpreting the last row of a table as containing
data? This should be simple but I'm stumped as to why it is doing this.

The column "A" does not have a formula in it, only data validation restricted
text can be entered, but columns I through L (end of table is in column L) do
have formulas that value to "" if A is empty. I tried the code suggested but
it errors out, probably because "A" column does not indeed have formulas, but
"I" through "L" do. Is there a way to work around that? Thanks.

"Bernie Deitrick" wrote:

If you have a formula in those cells, you will always end up at row 5000 no matter how the cell
appears.

But if the formula returns either a number or "" then you could use something like

Sub Macro1()
Dim myR As Range
Dim myC As Range

Set myR = Range("A2:A5000").SpecialCells(xlCellTypeFormulas, 1)
Set myC = myR.Areas(myR.Areas.Count).Cells(myR.Areas(myR.Are as.Count).Cells.Count)
myC.Select
MsgBox "The last cell with a number is in row " & myC.Row
End Sub


HTH,
Bernie
MS Excel MVP


"CoolBusiness" wrote in message
...
I have a table which ends at row 5000. Using the following line in a macro
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Select
goes to row 5000 each time even thought the last row with data is much
further up. Is Excel interpreting the last row of a table as containing
data? This should be simple but I'm stumped as to why it is doing this.

I thought of this afterwards. I do have a formula in another column of the
table with a value of "" if the value in the same row of "A" is blank. This
might be the problem, i.e. a formula in a cell. What would be a workaround
to get to the first "" value? Thanks for any help

"CoolBusiness" wrote:

I have a table which ends at row 5000. Using the following line in a macro
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Select
goes to row 5000 each time even thought the last row with data is much
further up. Is Excel interpreting the last row of a table as containing
data? This should be simple but I'm stumped as to why it is doing this.

I wrote this code which seems to work beautifully. My table's first
available row is 11 and ends at 5000. I couldn't make the End(xlUP) work in
the table if any cell within the row had a formula. This seems to be the
answer. If there's a better or less intensive way, please let me know.
Thanks.

Sub Determine_Last_Row()
'
' Determine_Last_Row Macro
'

Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For X = LastRow To 11 Step -1
If Cells(X, 1).Value < "" Then
Cells(X, 1).Offset(1).Select
GoTo EndRoutine
End If
Next X
EndRoutine:

End Sub



"CoolBusiness" wrote:

I have a table which ends at row 5000. Using the following line in a macro
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Select
goes to row 5000 each time even thought the last row with data is much
further up. Is Excel interpreting the last row of a table as containing
data? This should be simple but I'm stumped as to why it is doing this.