BEST way to delete rows based on cell value

[EchoPassenger]

I am using the following code to work backwards though a range, deleting rows if the cell does not contain a value beginning with "J"

In the past I have used several other ways to delete rows in a range, so I'm wondering... what the best way to do it?

Please Login or Register  to view this content.

My range consists of having a parent row followed by 6 child rows, and it's these child rows that are being deleted.
So would a better way be to block-delete the rows by just selecting/activating the entire range, then deselecting the 1st and every 7th row?

[Leith Ross]

Hello Echo Passenger,

Did you want the rows to shift up after they are deleted or can they remain blank?

[EchoPassenger]

Yes to delete and shift up the subsequent rows.

Although I have asked about the 'best' way in the OP, I am a big believer in brevity of code, so I would like to keep it as simple as possible.
In that regard, I have been playing around with a few alterations and currently the fastest (although the time to run differences are very minimal) version seems to be rather than test each cell for a value <> "J*", just delete an offset range of the below 6 rows then move down the next parent row (which has now) shifted up to the next row) and repeat.

Please Login or Register  to view this content.

Looking back at old code, I have used a different, far more verbose, loop as well as the AutoFilter to delete rows. I think what I have now is probably the best approach.

I am still curious though, could I select my entire range A3:A* then just deselect the first and every 7th row, then delete the remaining selection in one go? Is there even a 'remove from selection' command?

[Leith Ross]

Hello EchoPassenger,


This will delete the lines in blocks of 6. This assumes there are no blanks between the "parent" and it "child" lines.

Please Login or Register  to view this content.

[EchoPassenger]

Looks good, I'll incorporate this.

Is there any benefit to using Cells(Rows.Count, "A").End(xlUp).Row over say Range("A65536").End(xlUp).Row? Aside from not having to enter a range start?

[mikerickson]

This is a non-looping approach

Please Login or Register  to view this content.

[bulina2k]

Looks good, I'll incorporate this.

Is there any benefit to using Cells(Rows.Count, "A").End(xlUp).Row over say Range("A65536").End(xlUp).Row? Aside from not having to enter a range start?

not all worksheets have 65536 rows.

[EchoPassenger]

not all worksheets have 65536 rows.

I was asking more with regards to the general technique rather than the specifics of that line.

[mikerickson]

...
Is there any benefit to using Cells(Rows.Count, "A").End(xlUp).Row over say Range("A65536").End(xlUp).Row? Aside from not having to enter a range start?

In all versions of Excel that support VBA, every worksheet has 65536 (or more) rows.
However, the more recent versions have many more and might have more than 65536 rows of data.

If you are 100% sure that there will be nothing below row 65535, then there is no difference, but the Rows.Count approach is more certain.