Find in Excel VBA

  • Thread starter Thread starter Merkling, Steven
  • Start date Start date
M

Merkling, Steven

If you don't have to reinvent the wheel use

Data ----> Subtotals

HTH
-Merk


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.
 
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)
 
Back
Top