HOW TO SELECT SPECIFIC CELL

[K]

Row A B C ---Col
1 SS
2 DD
3 EE
4 FF
5 GG
6 HH
7 TT
8
9
10

I always had problem with selecting cells by macro. Accroding to the
table above what code should I write
1 - To select last value cell in column B
2 - To select one cell below of last value cell in column B
3 - To select left cell of last value cell in column B
4 - To select right cell of last value cell in column B
5 - To select one cell below left of the last value cell in column B
(which will be cell A8)
6 - To select one cell below right of the last value cell in column B
(which will be cell C8)
I know that code should be something like this "Cells("B1" &
Rows.Count).End(XlUp).Row.Select" but I am not quite sure. Please if
any body can help as by getting knowledge of selecting specific cells
it will be very helpful for me. Thanks

 

[Per Jessen]

Hi

1. Range("B1").End(xlDown)
2. Range("B1").End(xlDown).Offset(1,0)
3. Range("B1").End(xlDown).Offset(0,-1)
4. Range("B1").End(xlDown).Offset(0,1)
5. Range("B1").End(xlDown).Offset(1,-1)
6. Range("B1").End(xlDown).Offset(1,1)

Just notice that if you have an empty cells in your list code use :

Range ("B65536").End(xlUp)

to finde the very last used cell in the column.

Best regards,
Per

 

[Gord Dibben]

Start from here...................note the Offset(row, column) usage

1........ ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Select

2........ ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Select

3........ ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Offset(0, -1).Select

Note: you don't generally have to use the "Select" but I added for clarity.


Gord Dibben MS Excel MVP