An entity in my office makes a worksheet with tasks on it.

Sometimes there are 200.
Sometimes there are 300.
Usually it is somewhere in between, but it can be more or less.

I am writing a macro that does a variety of transformations to the data in
this sheet.

For simplicity, I have a lot of the transformations fill down to row 350 or
so. It's a nice round number and we'll almost never have more than 350 items
in the sheet.

Using the "search" feature on this forum, I've found how to identify the
last row with data. For the sake of my example, let's say 285 is the last row
with data.

What I *want* to do is select rows 286-350 and delete them to clean up the
bottom of the sheet. I've found a lot of code in this newsgroup for filling
in data down TO the last row, but nothing regarding selecting a range BELOW
the last row.

Example, again; something like this:

lastrange = Range("K500:A" & lastrow + 1)
Range(lastrange).Activate
ActiveCell.Delete

(I realize that exact code does nothing.) Anyway, the idea there is maybe to
select all the cells from A(lastrow) to K500 and delete them.

Thoughts are most welcome!

Thanks

Jim

firstrow = Range("A1").End(xlDown).Row +1

with range(cells(firstrow,"A"),cells(350,"A") )
..clearcontents
end with


"JaimeZX" wrote:

An entity in my office makes a worksheet with tasks on it.

Sometimes there are 200.
Sometimes there are 300.
Usually it is somewhere in between, but it can be more or less.

I am writing a macro that does a variety of transformations to the data in
this sheet.

For simplicity, I have a lot of the transformations fill down to row 350 or
so. It's a nice round number and we'll almost never have more than 350 items
in the sheet.

Using the "search" feature on this forum, I've found how to identify the
last row with data. For the sake of my example, let's say 285 is the last row
with data.

What I *want* to do is select rows 286-350 and delete them to clean up the
bottom of the sheet. I've found a lot of code in this newsgroup for filling
in data down TO the last row, but nothing regarding selecting a range BELOW
the last row.

Example, again; something like this:

lastrange = Range("K500:A" & lastrow + 1)
Range(lastrange).Activate
ActiveCell.Delete

(I realize that exact code does nothing.) Anyway, the idea there is maybe to
select all the cells from A(lastrow) to K500 and delete them.

Thoughts are most welcome!

Thanks

Jim

Wow! That's a quick reply!

Thanks very much. At first I thought there was an issue with the code but
then realized I had goofed the implementation a bit.

Thanks so much, it's perfect!

Jim

"Patrick Molloy" wrote:

firstrow = Range("A1").End(xlDown).Row +1

with range(cells(firstrow,"A"),cells(350,"A") )
.clearcontents
end with


"JaimeZX" wrote:

An entity in my office makes a worksheet with tasks on it.

Sometimes there are 200.
Sometimes there are 300.
Usually it is somewhere in between, but it can be more or less.

I am writing a macro that does a variety of transformations to the data in
this sheet.

For simplicity, I have a lot of the transformations fill down to row 350 or
so. It's a nice round number and we'll almost never have more than 350 items
in the sheet.

Using the "search" feature on this forum, I've found how to identify the
last row with data. For the sake of my example, let's say 285 is the last row
with data.

What I *want* to do is select rows 286-350 and delete them to clean up the
bottom of the sheet. I've found a lot of code in this newsgroup for filling
in data down TO the last row, but nothing regarding selecting a range BELOW
the last row.

Example, again; something like this:

lastrange = Range("K500:A" & lastrow + 1)
Range(lastrange).Activate
ActiveCell.Delete

(I realize that exact code does nothing.) Anyway, the idea there is maybe to
select all the cells from A(lastrow) to K500 and delete them.

Thoughts are most welcome!

Thanks

Jim