Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Senior Member
Join Date
11th May 2006
Posts
101

## Add X Rows Between Each List Item

i want to be able to drag a formula down and have it skip rows when doing so. please see example attached sheet for clarification. or if someone can suggest a way of inserting two rows inbetween data automatically that would work too.

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
21st February 2006
Location
London, UK
Posts
3,867

## Re: Skip Two Rows In Formula

I can't download your attachment right now but try selecting the first cell with the formula and the two below and then drag down those three cells with the Fill Handle. Does that work?
Last edited by Dave Hawley; December 2nd, 2006 at 12:53.

Excel Video Tutorials / Excel Dashboards Reports

3. Established Member
Join Date
6th September 2004
Posts
438

## Re: Skip Two Rows In Formula

Not quite sure if this is what you mean, but if you copy it down once then highlight the first 2 plus 2 blank rows you can then drag down.

ie
A1 1
A2
A3
A4 2

then highlight A1:A6 and drag down.

TJ

Edit: ignore this post as there is no need to do the first copy
Last edited by tinyjack; December 2nd, 2006 at 05:53.

4. Senior Member
Join Date
11th May 2006
Posts
101

## Re: Skip Two Rows In Formula

unless i am doing something wrong excel tries to skip those cells when you drag or copy. maybe the solution is a macro that inserts two rows after data. perhaps someone could help with this idea.

Excel Video Tutorials / Excel Dashboards Reports

5. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Skip Two Rows In Formula

Hi,

Try something like

VB:
```Sub Test()
Dim i   As Long, j  As Long
i = [a65536].End(xlUp).Row
For j = i To 2 Step -1
Rows(j + 1 & ":" & j + 2).Insert
Next j
End Sub

```

6. ## Re: Skip Two Rows In Formula

This macro assumes you data starts in A1.

VB:
```Sub AddRowsBetween()
Dim lLoop As Long

For lLoop = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
Cells(lLoop, 1).Range("A1:A2").EntireRow.Insert
Next lLoop

End Sub

```

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno