PDA

View Full Version : Solved: Find Last Used Cell In a Table



fredlo2010
06-01-2012, 05:19 PM
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.

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

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 :think:

Feedback is the best way for me to learn

Opv
06-01-2012, 06:05 PM
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.

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
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 :think:

Feedback is the best way for me to learn
Something like the following?


Range("A2500").End(xlUp).Select

fredlo2010
06-01-2012, 09:15 PM
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
06-01-2012, 09:58 PM
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
06-02-2012, 01:31 AM
If the table is not sparse at all you could use

With Range("A1")

.Offset(.CurrentRegion.Rows.Count - 1, .CurrentRegion.Columns.Count - 1).Select
End With

Bob Phillips
06-02-2012, 01:35 AM
If it is an actual table, you can also use

With ActiveSheet.ListObjects("Table1")

ActiveSheet.Cells(.ListRows.Count + 1, .ListColumns.Count + 1).Select
End With