Picking up the last non-empty cell in a given range
 

I have given a range (say a1.a100 which contain IF function in every cell) to
my users. They are to enter value into the cell everyday continuously.

Can anyone help me with VBA codes for me to pick up the last entered value
(say A42, A43 onwards are still empty) on anyday, to be linked to another
workbook?

I used the SpecialCells(xlCellTypeLastCell) feature, it doesn't work. It
always return A100, possibly because A100 contains an IF statement.

Thanks.

something like this :- ??

r = 1
While ActiveSheet.Cells(r, 1).Value < 0
r = r + 1
Wend
ActiveSheet.Cells(r, 1).Select


--
BrianB


------------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...info&userid=55
View this thread: http://www.excelforum.com/showthread...hreadid=375206

"zhj23" wrote:

I have given a range (say a1.a100 which contain IF function in every cell) to
my users. They are to enter value into the cell everyday continuously.

Can anyone help me with VBA codes for me to pick up the last entered value
(say A42, A43 onwards are still empty) on anyday, to be linked to another
workbook?

I used the SpecialCells(xlCellTypeLastCell) feature, it doesn't work. It
always return A100, possibly because A100 contains an IF statement.

Thanks.

Put this in a macro and it should find the next empty cell in Col A
Range("A65536").End(xlUp).Offset(1, 0).Select

I think you should reconsider what you are doing, but with the
information provided you appear to want the cell below the
last constant in Column A. The following will do that.

Sub afterlastconstant()
Dim rng As Range, L As Long, M As Long
Set rng = Columns("A:A").SpecialCells(xlCellTypeConstants, 23)
L = rng.Areas.Count
M = rng(L).Count
rng.Areas(L).Item(M).Offset(1, 0).Select
End Sub

The above solution has no loops, so should run very fast.
there is problem with Special cells with over 8,192 non-contiguous cells
in other words more than 4096 separate areas of contiguous constants,
which you are much more likely to encounter if you have alternate rows of data..

Another solution offered is dependent on there always being 65536 rows
since that number has changed in the past and may change again it should
not be a constant cells.rows.count should be used instead of 65536
http://www.mvps.org/dmcritchie/excel...ars.htm#macros
In any case it is the wrong solution for this thread, since cells with formulas
are definitely not empty and cell A100 has a formula.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"zhj23" wrote in message ...
I have given a range (say a1.a100 which contain IF function in every cell) to
my users. They are to enter value into the cell everyday continuously.

Can anyone help me with VBA codes for me to pick up the last entered value
(say A42, A43 onwards are still empty) on anyday, to be linked to another
workbook?

I used the SpecialCells(xlCellTypeLastCell) feature, it doesn't work. It
always return A100, possibly because A100 contains an IF statement.

Thanks.

Hi! All:

Thanks for all the suggestions. I am working on it now.

David: If every cell in my range has an IF function, can it still be
considered as
"xlCellTypeConstants"?


"zhj23" wrote:

I have given a range (say a1.a100 which contain IF function in every cell) to
my users. They are to enter value into the cell everyday continuously.

Can anyone help me with VBA codes for me to pick up the last entered value
(say A42, A43 onwards are still empty) on anyday, to be linked to another
workbook?

I used the SpecialCells(xlCellTypeLastCell) feature, it doesn't work. It
always return A100, possibly because A100 contains an IF statement.

Thanks.