Insert a row after every 5 rows posible?

[GUJUm0deL]

Hey, I was wondering is it possible in Excel to have a blank row inserted after every 5 rows automatically? I have a worksheet that has 1000 items, and i'd like to insert a blank row automatically after every 5 rows. Is that possible?

Thanks.


____________________________________
Just Imagine.

http://www.imajinarts.com

 

[SkipVought]

Hi,

If your data is a table, it is very unwise to insert empty rows. It destroys the table and makes feature like sort and filter much harder to perform.

If your objective is visual separation there are a number of other options like

1) make every 5th rowheight twice as high

2) use conditional formatting to shade each band of 5 rows alternately

just to suggest two.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[SimonDavis]

A macro would do it, but if you're not familiar with the code, you'd have to watch it or it'd keep going for 65000 rows . . .

Normally I'd record a macro, to insert a blank line, then add a little piece of code to get it to select 5 rows down, (offset) and repeat. You could put a for next ()or whatever the 2004 equivalent is) to stop it after 200 executions, ie 1000 rows.

 

[GUJUm0deL]

Hi, can someone provide me the code that does this?? Or, show me how to write a code like that??

Thanks...


____________________________________
Just Imagine.

http://www.imajinarts.com

 

[SkipVought]

Sub InsertEmptyRows()
'this is generally not a good idea
'a better choice is to change the row height
   rStart = InputBox("Start in what row?")
   If Not IsNumeric(rStart) Then Exit Sub
   rEnd = InputBox("How many rows?")
   If Not IsNumeric(rEnd) Then Exit Sub
   For r = rStart + 5 To CInt(rEnd) + CInt(rStart) + 1 Step 6
      Cells(r, 1).EntireRow.Insert shift:=xlUp
   Next
End Sub

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[GUJUm0deL]

Thanks for the code. I'll let you know when I use it.

'this is generally not a good idea
'a better choice is to change the row height

How can I change the row height automatically? The whole reason for doing it this way is to place some space between the large number for rows. If there is an easier way of doing this (and its automatically) then im open to suggestions/ideas.


____________________________________
Just Imagine.

http://www.imajinarts.com

 

[Tonyjstone]

Hi GUJU,
If you were to colour the entire 1000 row field pale grey, and then, with the selection highlighted, select Conditional Format, Formula is, then enter this
=MOD(ROW(),5)
click Format and select the colour White, then click OK
the selection will have every 5th row tinted grey.

 

[SkipVought]

Sub EmphasizeGroupsByRowHeight()
   rStart = InputBox("Start in what row?")
   If Not IsNumeric(rStart) Then Exit Sub
   rEnd = InputBox("How many rows?")
   If Not IsNumeric(rEnd) Then Exit Sub
   For r = rStart + 5 To CInt(rEnd) + CInt(rStart) + 1 Step 5
      Cells(r, 1).RowHeight = 24
   Next
End Sub

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}