Insert Blank Row Based on Value in Column A

K

Kevin D

Hi There,
I've searched through a number of topics on this, but could not find
anything similar to my problem.
I have numeric values in column A, and would like to insert the same
quantity of rows beneath -based on the value. The tricky part is that
the number of rows must be one less than the value. So if the value is
6, there should be 5 blank rows inserted beneath. If the value is 9,
there should be 8 blank rows inserted beneath.

A Macro would certainly be the right answer to this, however, I am
unfamilier with writing code.
Here is a before and after sense of what I'm looking for.

What I have......
A B
1 1 CAT
2 6 DOG
3 4 BOAT
4 2 ROCK
5 3 FOX
6 5 SHIRT
7 3 FROG

What I would like to have.....
A B
1 1 CAT
2 6 DOG
3 Blank row
4 Blank row
5 Blank row
6 Blank row
7 Blank row
8 4 BOAT
9 Blank row
10 Blank row
11 Blank row
12 2 ROCK
13 Blank row
14 3 FOX
15 Blank row
16 Blank row
18 5 SHIRT
19 Blank row
20 Blank row
21 Blank row
22 Blank row
23 3 FROG
24 Blank row
25 Blank row

Any assistance would be greatly appriciated.

Thanks in advance,
Kevin D
 
M

Martin Fishlock

Kevin this is a little routine that will do what you want based on the
details given starting at A1 with no headers.

Sub insert_rows_()
Dim row_start As Long
Dim row_end As Long
Dim row_cur As Long
Dim row_insert As Long
' assume data start at A1
' set the start and end rows
row_start = 1
row_end = Range("A1").CurrentRegion.Rows.Count
' work from the bottom up
For row_cur = row_end To row_start Step -1
'insert the rows below current row
For row_insert = Range("A" & row_cur) To 2 Step -1
Rows(row_cur + 1).Insert xlShiftDown
Next row_insert
Next row_cur
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top