How to find the last row

[Ragnar Midtskogen]

Hello,

I need to delete all rows of a worksheet, starting with a certain row.
I have looked for a property that would give the row count but have not
found any.
What is a reliable way to detect which is the last row that contains
anything, short
of stepping through each row and looking for data.

It looks like the UsedRange property of a WorkSheet do this, but I am not
sure how.
Would a For Each --- Next loop on the rows do the trick?
Once I have the first and last rows then I can delete all the rows in one
operation.

Any help on this would be appreciated.

Ragnar

 

[J.E. McGimpsey]

One way:

If you know that column A is filled for each (or at least the last)
row that has data and you want to start at row 10:


Range("A10:A" & Range("A" & _
Rows.Count).End(xlUp).Row).EntireRow.Delete

This "looks up" from the last row to find the first row from the
bottom with data - the last row from the top .

 

[Ragnar Midtskogen]

Thank you,

I guess I did not make it clear that I don't know which column has data in
the last row.

But I think you came up with the answer I was looking for, the last row is
Rows.Count.
The help files did not indicate that the Rows property had a Count
subproperty.

Ragnar

 

[Earl Kiosterud]

Ragnar,

About the Count property -- if you put the cursor on Rows (Range(x).Rows),
and press F1, it gives you help on the Rows property, saying it returns a
range object. You can click the Range hyperlink, which gives you help on
the Range object (collection, actually, though Range can't seem to decide if
it's a collection or single object -- doesn't really matter here). Now the
Properties are listed for the range object, which applies to the Rows object
you started with. You'll see Count listed as one of the properties.

Here's an alternate way, though it still looks only in column A for stuff.
There can't be any empty cells along the way from A10 down:
Range(Range("A10"), Range("A10").End(xlDown)).EntireRow.Delete xlUp

If there might be empty cells, and you still want to get to the last one,
use:
Range(Range("A10"), Range("A65536").End(xlUp)).EntireRow.Delete xlUp

Neither uses concatenation. Rather, they use a range(here, there)
construct. The second is a little sloppy, for if Excel ever increases the
rows from 65536, it could fail. Pigs will fly first, I suspect. But it
illustrates the point.

 

[Ragnar Midtskogen]

Hello Earl,

Thank you for your comments.

I did find the Count property in the help, but it looks like the problem
here is to
determine the last row that has any data in any of the columns.
It turns out that none of the columns are without empty cells. In fact, all
the samples
I have seen has one completely blank row about 20 rows up from the bottom.

I tried Mr. McGimpsey's code and it deleted all row from 4 down to the
completely
blank row I mentioned above, leaving about 25 of the bottom rows. I had
specified
it should delet from A6, so I can't make sense of what happened.

I also tried your last example, it deletes from row 10 down to the first
completely
blank row, then leaves the last 20 odd rows. Almost the samee results as Mr.
McGimpsey's code.

I thought I could just find the last row, then delete all rows from 6 to the
last one,
and use Range("6:lastrow").delete, but that gives an a error 'Object doesn't
support
this property or method'

This Excel seem to me a strange beast, Word is a piece of cake by
comparison.

What I am trying to accomplish is to break up large spreadsheets containing
sales
data for all divisions into separate workbook files for each division. Each
sheet
has a header sectiion with column titles and it contains hidden columns.
Each of
visible columns has a distinct width.
I first tried, working manually, selecting out the header and the rows for
each division
and pasting those into a new sheet, but the formatting disappeared. Then, I
found
that if I deleted all rows except the header rows and the first data row,
then
pasted the rows for a division into that data row, the formatting was kept.
Now I am trying to do this with code.

I recorded a macro of what I did, but of course, the tricky part is the
selection
and that is done by hand.

Ragnar

 

[Earl Kiosterud]

Ragnar,

I've looked again at your original post.

I need to delete all rows of a worksheet, starting with a certain row.

Seems to me we can just trash everything from the specified row on down.
Range(Range("A10"), Cells(Cells.Rows.Count, 1)).EntireRow.Delete

No?

Or if you'd rather use an index to the starting row:
StartRow = 10
Range(Cells(StartRow, 1), Cells(Cells.Rows.Count, 1)).EntireRow.Delete

What I am trying to accomplish is to break up large spreadsheets containing
sales
data for all divisions into separate workbook files for each division.

I presume you meant to say "a separate workbook file for each division."
You won't be able to do any summarizing readily across divisions if you
split the data like that. I can't comment much more about your task -- I
don't understand what you're starting with and what you want to have when
you're done.

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

 

[Ragnar Midtskogen]

Thank you Earl!

Seems to me we can just trash everything from the specified row on down.
Range(Range("A10"), Cells(Cells.Rows.Count, 1)).EntireRow.Delete

That did it!

I also tried using a recorded macro, just to see what it would do, and of
course it worked too.

Rows("6:437").Select
Range("D6").Activate
Selection.Delete Shift:=xlUp

But that leaves the task of determining where the data stops. I also do not
like to use the Selection
if I can avoid it. This code will be running in the background with no user
interaction other than
selecting the file to be broken up. Even that may be automated eventually.

Seems like there is more than one way to skin a cat when it comes to
manipulating Excel sheets..

I presume you meant to say "a separate workbook file for each division."
You won't be able to do any summarizing readily across divisions if you
split the data like that.

That is no problem, these people use Excel just as a text processor that
allows a convenient way
to arrange manually entered data in rows and columns. There are no formulas.
Kind of strange
but that is their choice.

Ragnar