last cell of a column 1

[kajal]

I just wanted know how can i get to the last cell on a certain column in an excel spread sheet...its not the last cell of the worksheet but the last cell of a certain column.I would appreciate it if anyoone could help me with the code
Thanks
Kajal

 

[ide]

i don't know what "last cell" means, but
if you want to find the last not empty cell in the current col, then:

Sub asdf()
Dim i As Long
i=0
While Not IsEmpty(ActiveCell.Offset(i, 0))
i = i + 1
Wend
if i=0 then i=1
MsgBox ActiveCell.Offset(i-1, 0).Address
End Sub

ide

 

[ilses]

Alternatively, without looping the last non-blank cell in column A can be found:

Range("A65535&quot.End(xlUp).Select

IS

 

[ide]

ilses,
yes, your code is much better
Apply it on the active column:

MsgBox Range(Cells(65536, ActiveCell.Column).Address).End(xlUp).Address

 

[kajal]

I am sorry about the question i asked earlier. The thing is that i am not trying to get the last cell in a column...but instead i need to count how many non empty cell are there in the column.
Thanks
Kajal

 

[ide]

Khmmm...now, try it again


dim lastRow, numNonBlanks, i as long
lastRow=Range(Cells(65536, ActiveCell.Column).Address).End(xlUp).row
numNonBlanks=0
i=0
range(cells(1,activecell.column)).address).activate
While i<lastRow
if not isempty(activecell.offset(i,0)) then _
numNonBlanks+numNonBlanks+1
i = i + 1
Wend
msgbox numNonBlanks

i hope it's right
ide

 

[kajal]

Thanks ide...but there is a syntax problem with your code.
It gives me an expected error on the line
Range(Cells(1, ActiveCell.Column)).Address).Activate
Are u sure this is the correct syntax for thia command.
Thanks
Kajal

 

[ide]

no. it is:

range(Cells(1, ActiveCell.Column).Address).Activate