I need to find the first row in column X where the value = "". By
value I mean the value of the formula. I have a formula in 1 through
65536 as I don't know how many rows the user is going to need from one
day to the next.

Any ideas??


TIA

First off, why don't you just set up your range of existing data as a list?
Then when you need to add a new entry, you just add an entry to the list and
any formulas above it (like the one in column X) are automatically applied
to the new entry. Populating all 65,536 rows is not efficient.

From an Oct 2004 posting by Michael Bauer:
This sample finds the first cell in column 24 from buttom up that is *not*
empty. The next cell then is empty of course.

dim rn as excel.range

set rn=Worksheets.Columns(24).Find("*", , xlValues, xlWhole, xlByRows,
xlPrevious)
if not rn is nothing then
set rn=rn.offset(1,0)
else
' the last row (65536) isn´t empty
endif

"tig" wrote in message
ups.com...
I need to find the first row in column X where the value = "". By
value I mean the value of the formula. I have a formula in 1 through
65536 as I don't know how many rows the user is going to need from one
day to the next.

Any ideas??


TIA

Dim X as long
for X=1 to 65536
If cells(X,"x")="" then
msgbox x
exit for
end If
Next X

tig wrote:
I need to find the first row in column X where the value = "". By
value I mean the value of the formula. I have a formula in 1 through
65536 as I don't know how many rows the user is going to need from one
day to the next.

Any ideas??


TIA

KC Rippstein wrote:
First off, why don't you just set up your range of existing data as a list?
Then when you need to add a new entry, you just add an entry to the list and
any formulas above it (like the one in column X) are automatically applied
to the new entry. Populating all 65,536 rows is not efficient.

From an Oct 2004 posting by Michael Bauer:
This sample finds the first cell in column 24 from buttom up that is *not*
empty. The next cell then is empty of course.

dim rn as excel.range

set rn=Worksheets.Columns(24).Find("*", , xlValues, xlWhole, xlByRows,
xlPrevious)
if not rn is nothing then
set rn=rn.offset(1,0)
else
' the last row (65536) isn´t empty
endif

"tig" wrote in message
ups.com...
I need to find the first row in column X where the value = "". By
value I mean the value of the formula. I have a formula in 1 through
65536 as I don't know how many rows the user is going to need from one
day to the next.

Any ideas??


TIA

Thanks for the post KC. I think that will do it. I'm interested in
learning a little more about your range as a list idea. The column
that I'm actually going after is column "D". I just used X as a
variable.

My formula in D2:D65536 is IF(C# = "", "", "This, that and whatever")

One day the user may import 500 records another day they might have
5000. In your idea, are you saying don't populate the "D" cell with
the formula if the respective "C" cell is empty. I'm not sure I
understood your meaning, but I'm very interested in making this process
more efficient.

Could you give me some additional detail on your idea and/or some code
to start with?

Thanks again for the help.

Zone wrote:
Dim X as long
for X=1 to 65536
If cells(X,"x")="" then
msgbox x
exit for
end If
Next X

tig wrote:
I need to find the first row in column X where the value = "". By
value I mean the value of the formula. I have a formula in 1 through
65536 as I don't know how many rows the user is going to need from one
day to the next.

Any ideas??


TIA

Thanks Zone. That works too.

I am thinking very basic here. When you select your data in your sheet, go
to Data - List - Create List.
Then when someone has to import records, they can copy all the data values
to be imported, go to the blue asterisk and hit enter.

This assumes, of course, that your data is columns A:C and columns
D:whatever are your formulas. Data needs to be contiguous, then formulas to
the right of it, in order to make copying and pasting a one-touch manual
operation. When your rows of data are pasted, the formulas in D, E, etc.
are automatically dragged down for the new recordsets.


"tig" wrote in message
s.com...
KC Rippstein wrote:
First off, why don't you just set up your range of existing data as a
list?
Then when you need to add a new entry, you just add an entry to the list
and
any formulas above it (like the one in column X) are automatically applied
to the new entry. Populating all 65,536 rows is not efficient.

From an Oct 2004 posting by Michael Bauer:
This sample finds the first cell in column 24 from buttom up that is *not*
empty. The next cell then is empty of course.

dim rn as excel.range

set rn=Worksheets.Columns(24).Find("*", , xlValues, xlWhole, xlByRows,
xlPrevious)
if not rn is nothing then
set rn=rn.offset(1,0)
else
' the last row (65536) isn´t empty
endif

"tig" wrote in message
ups.com...
I need to find the first row in column X where the value = "". By
value I mean the value of the formula. I have a formula in 1 through
65536 as I don't know how many rows the user is going to need from one
day to the next.

Any ideas??


TIA

Thanks for the post KC. I think that will do it. I'm interested in
learning a little more about your range as a list idea. The column
that I'm actually going after is column "D". I just used X as a
variable.

My formula in D2:D65536 is IF(C# = "", "", "This, that and whatever")

One day the user may import 500 records another day they might have
5000. In your idea, are you saying don't populate the "D" cell with
the formula if the respective "C" cell is empty. I'm not sure I
understood your meaning, but I'm very interested in making this process
more efficient.

Could you give me some additional detail on your idea and/or some code
to start with?

Thanks again for the help.