Reaching the next cell after the last data value in a column

[dsudhakar]

1

2

3

4


How to Reach the next cell automatically after the last data value (4) in the above column?

 

[sgmpatnaik]

@dsudhakar


Hi


Try This

[pre]

Private Sub Worksheet_Activate()
Dim NextRow As Long
NextRow = Range("A65536").End(xlUp).Row + 1
Cells(NextRow, 1).Select
End Sub

or if you want select the last entered row then Try This


Private Sub Worksheet_Activate()
Range("A65536").End(xlUp).Select
End Sub

[/pre]
Hope it is clear other wise give some more details


Thanks


SP

 

[Faseeh]

Hi dsudhakar,


You can use CountA() as well if you are using formulas.


Faseeh

 

[dsudhakar]

Hi Faseeh

I am using an existing macro. I like to add your macro into my existing macro.

Problem is

1

2

3

4


a

b

c

I am in cell where 1 is written.How to Reach the cell after the data value (4) automatically in the above column? Remember there are more values down below, after a few blank cells. I want to reach the cell exactly after 4 from the cell where 1 is written. All are in the same column.

Thanks

 

[Faseeh]

Hi dsudhakar,


Mine was not a macro it was a formula, you can use sgmpatnaik's solution if you are working with macros.


Regards,

 

[SirJB7]

@sgmpatnaik


Hi!


Your code works for 2003 Excel version or later with worksheets with less than 64K rows. A workaround is changing the line:

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

by:

-----

[pre]

With ActiveSheet.Cells(Rows.Count, 1)
If .Value = "" Then
.End(xlUp).Select
Else
.Select
End If
End With

[/pre]
-----

to make it independent of file types (.xls or .xlsm), worksheet row sizes and Excel versions.


Regards!

 

[sgmpatnaik]

@SirJB7


Hello


Thanks for your guidelines sir, keep going


Thanks


SP

 

[dsudhakar]

Hi @sgmpatnaik


I have entered a stand-alone macro with your script

-----

With ActiveSheet.Cells(Rows.Count, 1)

If .Value = "" Then

.End(xlUp).Select

Else

.Select

End If

End With

-----

1

2

3

4


a

b

c


I am in cell with value 1. Macro is taking me to the last cell with value in the column, which is c. But I want to reach the blank cell after cell with value 4, from the cell with value 1. Pl. help.

 

[Marc L]

ActiveCell.End(xlDown).Select

            …

 

[dsudhakar]

Marc L

I want to reach THE BLANK CELL AFTER cell with value 4, from the cell with value 1. Your macro is only reaching me to the cell with value 4 .

 

[Marc L]

Brain assist code :

ActiveCell.End(xlDown)(2).Select

 

[dsudhakar]

Marc L

It has worked

ActiveCell.End(xlDown)(2).Select

Thank you very much.

 

[Hui]

or

ActiveCell.End(xlDown).offset(1).Select