If you don't have to reinvent the wheel use

Data ---- Subtotals

HTH
-Merk



11/25/03 12:37PM
I am tearing my hair our about the following:
I got a big excel table with dates in the first column and
would like to sort all the entries reversely and then
create subtotals for each date, which I am doing within a
VBA macro. However, I don't seem to be able to select all
the data. At present I do something like Range
("A1:J14000").Select because I know there are less than
14000 rows used, but how can I just select all the cells
with data in them. (by data I mean for example a non empty
date field in the first column).
I was trying to use the Find command but had no luck.
Any clues?

I am tearing my hair our about the following:
I got a big excel table with dates in the first column and
would like to sort all the entries reversely and then
create subtotals for each date, which I am doing within a
VBA macro. However, I don't seem to be able to select all
the data. At present I do something like Range
("A1:J14000").Select because I know there are less than
14000 rows used, but how can I just select all the cells
with data in them. (by data I mean for example a non empty
date field in the first column).
I was trying to use the Find command but had no luck.
Any clues?

Find a colum where you are sure all rows are NOT empty
selct the first rwo on that colum
lets say colum c5
Range ("c5").select
ActiveCell.End(xlDown).Select
xLastRow= Activecell.row
Range("a5:h" & xLastRow).select
assuming h is your last colum this will select the range
of data you need.



-----Original Message-----
I am tearing my hair our about the following:
I got a big excel table with dates in the first column
and
would like to sort all the entries reversely and then
create subtotals for each date, which I am doing within a
VBA macro. However, I don't seem to be able to select all
the data. At present I do something like Range
("A1:J14000").Select because I know there are less than
14000 rows used, but how can I just select all the cells
with data in them. (by data I mean for example a non
empty
date field in the first column).
I was trying to use the Find command but had no luck.
Any clues?
.

a slightly different approach that passes over embedded blanks and thus
doesn't ignore trailing rows

Range("A5:H" & Cells(Rows.Count,"C").End(xlUp).Row).Select


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andres Angel" wrote in message
...
Find a colum where you are sure all rows are NOT empty
selct the first rwo on that colum
lets say colum c5
Range ("c5").select
ActiveCell.End(xlDown).Select
xLastRow= Activecell.row
Range("a5:h" & xLastRow).select
assuming h is your last colum this will select the range
of data you need.



-----Original Message-----
I am tearing my hair our about the following:
I got a big excel table with dates in the first column
and
would like to sort all the entries reversely and then
create subtotals for each date, which I am doing within a
VBA macro. However, I don't seem to be able to select all
the data. At present I do something like Range
("A1:J14000").Select because I know there are less than
14000 rows used, but how can I just select all the cells
with data in them. (by data I mean for example a non
empty
date field in the first column).
I was trying to use the Find command but had no luck.
Any clues?
.