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

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

"K" skrev i meddelelsen
...
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

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

On Sun, 13 Apr 2008 11:28:08 -0700 (PDT), K wrote:

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