Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
"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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
place empty cell with zero | Excel Worksheet Functions | |||
Is there a way to go to next empty cell in a formula | Excel Worksheet Functions | |||
macro to look for empty cell | Excel Discussion (Misc queries) | |||
How do I set a cell to "Empty" so that it does not display in a ch | Charts and Charting in Excel | |||
make a cell empty based on condition | Charts and Charting in Excel |