how to delete unnecessary rows ??

I want to delete unnecessary rows in a 332324 row s/s containing 30000+ ranges of data of differing sizes.
The minimum necessary requirement for each range is 4 rows. There should also be one blank row between each range.
Does anyone know code which will carry out such an instruction please?
By way of illustration, I enclose a small attachment.
Big thanks.
Colwyn.

You really need to delete all those empty modules!

Try this code

Please Login or Register  to view this content.

You really need to delete all those empty modules!

Try this code

Please Login or Register  to view this content.

Originally Posted by colwyn

Bob Phillips, thanks a lot but your code sets a limit of 4 rows per range whereas I wanted a minimum of 4 rows.

RoyUK, thanks. That does exactly what I wanted it to do.
Just one thing, when you say "You really need to delete all those empty modules!" do you mean blank columns??
Big thanks.
Colwyn.

I mean that you have several empty modules in the workbook -empty code modules

If you open the VB Editor you will see that you have a list of modules - most of them are empty, i.e. they contain no code

You must have added them from the VBA Insert menu

Sheet1 as used in Roy's code is the CodeName of a worksheet -- what you see in the project Explorer window to the left of the sheet name. A workbook with two sheets starts out as:

Sheet1 (Sheet1)
Sheet2 (Sheet2)

Rename Sheet1 as mySheet and you'll see

Sheet1 (mySheet)
Sheet2 (Sheet2)

The CodeName is unchanged; only the sheet name changes. Delete Sheet1, rename Sheet2 as Sheet1, and you have

Sheet2 (Sheet1)

Poof -- no sheet with CodeName Sheet1, even though you may have a sheet named Sheet1, and the code errors.

Sooo .... change the code to either use the CodeName for the worksheet of interest, or use the worksheet name:

Please Login or Register  to view this content.