Hello again!

Recently I had this problem: My macro fills in data
into a column with the length of the range called "vol1"
daily. Sometimes, if no value is found from the data
sources, it leaves cells blank. Now, I'd like to find the
end of the list (last entry) but can't use the specialCells
(xlCellTypeBlanks) command (because an empty cell might
not be the last entry). Is there any other option? I think
the macro needs to check if from one empty cell on all
following cells (until the end of the range) are empty too.

... and tried the following:
iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

It works in general, but unfortunately not in my document.
I can't find the error. Does anybody know what the problem
might be? iLastRow always turns out as 1 which is
incorrect. Maybe excel cant't find data, but I don't know
why ...

Thanks for helping me,
Maria

Maybe col A is blank and you need to change to B or C or ?
What it is doing is going to the bottom of the column and then going up till
something is found.

--
Don Guillett
SalesAid Software

"Maria" wrote in message
...
Hello again!

Recently I had this problem: My macro fills in data
into a column with the length of the range called "vol1"
daily. Sometimes, if no value is found from the data
sources, it leaves cells blank. Now, I'd like to find the
end of the list (last entry) but can't use the specialCells
(xlCellTypeBlanks) command (because an empty cell might
not be the last entry). Is there any other option? I think
the macro needs to check if from one empty cell on all
following cells (until the end of the range) are empty too.

.. and tried the following:
iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

It works in general, but unfortunately not in my document.
I can't find the error. Does anybody know what the problem
might be? iLastRow always turns out as 1 which is
incorrect. Maybe excel cant't find data, but I don't know
why ...

Thanks for helping me,
Maria

Maria,

You could also try

With Activesheet
iLastRow =
..Cells(.Rows.Count,.Usedrange.Cells(1,1).Column). End(xlUp).Row
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Don Guillett" wrote in message
...
Maybe col A is blank and you need to change to B or C or ?
What it is doing is going to the bottom of the column and then going up
till
something is found.

--
Don Guillett
SalesAid Software

"Maria" wrote in message
...
Hello again!

Recently I had this problem: My macro fills in data
into a column with the length of the range called "vol1"
daily. Sometimes, if no value is found from the data
sources, it leaves cells blank. Now, I'd like to find the
end of the list (last entry) but can't use the specialCells
(xlCellTypeBlanks) command (because an empty cell might
not be the last entry). Is there any other option? I think
the macro needs to check if from one empty cell on all
following cells (until the end of the range) are empty too.

.. and tried the following:
iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

It works in general, but unfortunately not in my document.
I can't find the error. Does anybody know what the problem
might be? iLastRow always turns out as 1 which is
incorrect. Maybe excel cant't find data, but I don't know
why ...

Thanks for helping me,
Maria

No, that's not it. The macro writes the data into
column "A" before using the command
iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
Thanks anyway ...

Can there be another problem?

Maria
-----Original Message-----
Maybe col A is blank and you need to change to B or C or ?
What it is doing is going to the bottom of the column and
then going up till
something is found.

--
Don Guillett
SalesAid Software

"Maria" wrote in
message
...
Hello again!

Recently I had this problem: My macro fills in data
into a column with the length of the range called "vol1"
daily. Sometimes, if no value is found from the data
sources, it leaves cells blank. Now, I'd like to find
the
end of the list (last entry) but can't use the
specialCells
(xlCellTypeBlanks) command (because an empty cell might
not be the last entry). Is there any other option? I
think
the macro needs to check if from one empty cell on all
following cells (until the end of the range) are empty
too.

.. and tried the following:
iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

It works in general, but unfortunately not in my
document.
I can't find the error. Does anybody know what the
problem
might be? iLastRow always turns out as 1 which is
incorrect. Maybe excel cant't find data, but I don't
know
why ...

Thanks for helping me,
Maria


.

I'd like to. What does the command stand for? I don't
understand it properly. I've never heard
of "Usedrange" ... Maybe you could explain it whenever you
find some time.
Thanks a lot,

Maria


-----Original Message-----
Maria,

You could also try

With Activesheet
iLastRow =
..Cells(.Rows.Count,.Usedrange.Cells(1,1).Column) .End
(xlUp).Row
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)

Maria,

As Don explained, the original formula started from the bottom of the
spreadsheet in column A, and worked its way up until it finds a non-empty
row. This way we get the last non-empty cell, even if there some embedded
empty cells in-between.

But, the column, A, was hard-coded. So what I did was to take the first
column (Cells(1,1).Column) of the UsedRange (the rectangular part of the
spreadsheet bounding the cells that have been used) instead of hard-coded A.
The idea is that it should pick up the first real column regardless of where
used.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Maria" wrote in message
...
I'd like to. What does the command stand for? I don't
understand it properly. I've never heard
of "Usedrange" ... Maybe you could explain it whenever you
find some time.
Thanks a lot,

Maria


-----Original Message-----
Maria,

You could also try

With Activesheet
iLastRow =
..Cells(.Rows.Count,.Usedrange.Cells(1,1).Column) .End
(xlUp).Row
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)