PDA

View Full Version : Delete Rows In A Sheet



tejasdave
04-09-2012, 04:44 AM
Hello All,

I have made a macro which auto - filters on certain criteria and copies the data to another sheet.

Now for example after filtering i am left with 10 rows it will copy those 10 rows and paste to another sheet which might have more than 10 rows.

So i want a macro which will delete all rows after 10 rows.

i already have a count of filtered rows available.

Regards,

Tejas Dave

Bob Phillips
04-09-2012, 06:00 AM
What are you trying to delete, the 10 rows copied, the rows in the target sheet, the source sheet, or what? Before you copy, after you copy?

tejasdave
04-10-2012, 12:39 AM
I am trying to delete all rows execpt the 10 which were copied and pasted.

Bob Phillips
04-10-2012, 12:42 AM
Then why not just delete the source worksheet? If you need that data twice, copy the filtered data to a new worksheet as well and delete the original.

tejasdave
04-10-2012, 12:49 AM
I cannot as the source sheet has formula's and links in it.i am pasting to a specific range starting from cell B2.

I already have a code which tells me the number of rows that were copied.is it possible to get a code which removes all rows except for that number of rows i.e 10 rows.

Bob Phillips
04-10-2012, 01:02 AM
I can't see why the formulas of the required rows would not be retained?

But assuming we don't, I would still address it from the other side. Filter the data by your criteria, copy the filtered rows. Then re-filter by NOT your criteria, and delete the filtered rows.

tejasdave
04-10-2012, 01:05 AM
here is what i do :

* Filter my criteria.
* copy the remaining data
* paste to another sheet to a specific range. ( the sheet will always have old data in it)

now after i paste the data to another sheet i want to delete all rows except the ones i pasted.

Kindly assist.

Regards,

Tejas Dave

Bob Phillips
04-10-2012, 01:34 AM
Did you read post #6?

tejasdave
04-10-2012, 01:56 AM
yes i did thats what i am trying to explain why i cannot use that approach.