Solved: Find Last Used Cell In a Table

[fredlo2010]

Hi,

Is there a way to select the last used cell in a table?

There is a possibility that my data has some empty spaces in it. No more than two consecutive rows.

I was thinking to select a cell as long as its empty and its value is equal to the one below.

[VBA]Dim i As Variant


For Each i In Range("A1:A207")
If Cells(i, 1).Value = "" And Cells(i, 1) = Cells(i + 1, 1) Then
Cells(i, 1).Select
End If
Exit For

Next i[/VBA]

This is my extremely inexperienced code. It does not work but you get the idea. Its a little frustrating when you have the algorithm in your head and you just don't have or know the tools to implement it.

I guess practice will help me with that part

Feedback is the best way for me to learn

[Opv]

Hi,

Is there a way to select the last used cell in a table?

There is a possibility that my data has some empty spaces in it. No more than two consecutive rows.

I was thinking to select a cell as long as its empty and its value is equal to the one below.

[vba]Dim i As Variant


For Each i In Range("A1:A207")
If Cells(i, 1).Value = "" And Cells(i, 1) = Cells(i + 1, 1) Then
Cells(i, 1).Select
End If
Exit For

Next i[/vba]
This is my extremely inexperienced code. It does not work but you get the idea. Its a little frustrating when you have the algorithm in your head and you just don't have or know the tools to implement it.

I guess practice will help me with that part

Feedback is the best way for me to learn

Something like the following?

[vba]
Range("A2500").End(xlUp).Select
[/vba]

[fredlo2010]

Ovp,

If I use that it will select the last cell, row in a table, it does not matter if its empty or not.

[jolivanes]

Try this

   With Sheets("Info")
    .Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2).End(xlUp).Offset(1).Select
   End With

Change Sheet and Column references as required.

[Bob Phillips]

If the table is not sparse at all you could use

[VBA] With Range("A1")

.Offset(.CurrentRegion.Rows.Count - 1, .CurrentRegion.Columns.Count - 1).Select
End With
[/VBA]

[Bob Phillips]

If it is an actual table, you can also use

[VBA] With ActiveSheet.ListObjects("Table1")

ActiveSheet.Cells(.ListRows.Count + 1, .ListColumns.Count + 1).Select
End With
[/VBA]