Excel: Macro to insert blank rows 2

[nicks60610]

I need some assistance with an Excel Macro, I can record the macro to insert blank rows every x row, but I need to do this:

I need from the top...20 groups of 3 rows, then 50 groups of 8 rows and the rest done in groups of 4

So I have approx 2000 rows that I need to do this too, and it is not always the same amount of rows, so the macro needs to count down the row, insert, continue counting, insert, etc. Has anyone done anything like this before, or even point me in the right direction and I will get going

Thanks,

 

[SkipVought]

Hi,

May I ask why you are inserting empty rows?

Ordinarily, empty rows can cause processing problems when using many of Excel's built-in features.

Skip,

_{[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors}

 

[nicks60610]

I am inserting blank rows for formatting so that when I print the report I have several sections (easier to read for the persons using it on the production floor).

 

[SkipVought]

THEN...

set the row height and cell formatting for that row.

It will make life easier in the long run.

Turn on your macro recorder and have at it.

Skip,

_{[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors}

 

[nicks60610]

I did record the macro, but as I am nearing over 1000 rows, it takes some time, what can I do to say for example, insert rows every group of 3 until Row 81, then insert row every group of 8 until Row 580, etc.

I tried something like this, but that did not work...

Do Until ActiveCell.Row = 1
Insert blank row.
Range(ActiveCell.Offset(-3, 0), ActiveCell.Offset(81, 0)).Select
Selection.EntireRow.Insert

ActiveCell.EntireRow.Insert shift:=xlDown
Move up one row.
ActiveCell.Offset(-3, 0).Select

Do Until ActiveCell.Row = 81

Loop

 

[SkipVought]

I [red]STRONGLY[/red] advise against inserting empty rows.

As I formerly suggested, record a macro to change the row height and formatting to accomplish the formatting for your report.

Skip,

_{[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors}

 

[AnotherHiggins]

nicks60610,

Heed Skip's advice. He knows what he is talking about.

You will make your life MUCH easier if you don't introduce loads of empty rows in your data.

As for Skip's suggestion, here's a tip: right click on a row and choose Row Height. Use that for your standard height. Let's say it's 12.75. So the equivalent of inserting 3 empty rows would be to set a row height to 12.75*4 = 51 (that's three empty rows plus the row where the data resides).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[nicks60610]

You're right, it does work better that way , thanks again!!!