Insert blank row and copy row to new blank row

[DTMnBHM]

I'm trying to manipulate a spreadsheet with approx 1600 rows of data. I need to insert 1 blank row in between a set or rows, then copy the data above, or below (I'm not sure if when the new row is inserted if it goes above or below the selected rows to copy), into the new blank row. New to the forum and apologize in advance if this has been discussed. I searched and couldn't find my exact need.

 

[Skyybot]

A blank row can be added above, or below, any row number. What determines where the row should be added. Will it be the same number of rows until the end of the Sheet? When data is copied, will it only be one row, or multiple? Details man!

 

[DTMnBHM]

A blank row can be added above, or below, any row number. What determines where the row should be added. Will it be the same number of rows until the end of the Sheet? When data is copied, will it only be one row, or multiple? Details man!

Thanks...I'm trying.

It doesn't matter if it's above or below.

Basically I need the data in a row copied into the new blank rows directly above or below.

For some portion of the spreadsheet I only need 1 blank row added (in between multiple rows) and copied. Some I will need 2 blank rows added and copied, and then the remaining will be 3.

For instance, lets say for rows 1-100. I need a blank row inserted between each, and the data from the original 100 rows copied into the new blank rows.

Then, for rows 101-200. I need 2 blank rows inserted between each, and the data from each row copied into the 2 blanks inserted between.

And so on

 

[Skyybot]

What you are saying makes no sense to me. I have included a visual representation (truncated 10 rows = 100 rows) in my understanding of your request. If it is accurate, please let me know.

Original Data

Book1
	A
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
21	21
22	22
23	23
24	24
25	25
26	26
27	27
28	28
29	29
30	30
31	31
32	32
33	33
34	34
35	35
36	36
37	37
38	38
39	39
40	40
Sheet1

Changed Data

Book1
	A
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
9	5
10	5
11	6
12	6
13	7
14	7
15	8
16	8
17	9
18	9
19	10
20	10
21	10
22	11
23	11
24	11
25	12
26	12
27	12
28	13
29	13
30	14
31	14
32	14
33	15
34	15
35	15
36	16
37	16
38	16
39	17
40	17
41	17
42	18
43	18
44	18
45	19
46	19
47	19
48	20
49	20
50	20
51	21
52	21
53	21
54	22
55	22
56	22
57	23
58	23
59	22
60	24
61	24
62	24
63	25
64	25
65	25
66	26
67	26
68	26
69	27
70	27
71	27
72	28
73	28
74	28
75	29
76	29
77	29
78	30
79	30
80	30
81	31
82	31
83	31
84	32
85	32
86	32
87	33
88	33
89	33
90	34
91	34
92	34
93	35
94	35
95	35
96	36
97	36
98	36
99	37
100	37
101	37
102	38
103	38
104	38
105	39
106	39
107	39
108	40
109	40
110	40
111
112
Sheet1

 

[DTMnBHM]

Yes. Only difference is there's 6 columns per row with data and I need all data in the row copied to the new blank row(s)

 

[Coyotex3]

Hmmm, who will be adding the blank rows? Like will you or someone else be manually inserting new rows into the sheet with the data?

 

[DTMnBHM]

Hmmm, who will be adding the blank rows? Like will you or someone else be manually inserting new rows into the sheet with the data?

I hope not. That's what I'm trying to figure out if it's something I can do using a script or something. I know how to manually add rows in between others rather quickly, but it's the copy of the data that would take me forever to do manually

 

[Coyotex3]

In that case, I'm with @Skyybot in that we need more information. What determines the amount of rows that will be added? How would we know which rows will need 1 blank row in between and which will need 2 etc. Unless we know this, we can't build a formula or write a code to assist.

Maybe I can provide a code that will look at your data and copy the data from the rows above as you need. But it wouldn't insert the rows for you.

 

[DTMnBHM]

In that case, I'm with @Skyybot in that we need more information. What determines the amount of rows that will be added? How would we know which rows will need 1 blank row in between and which will need 2 etc. Unless we know this, we can't build a formula or write a code to assist.

Maybe I can provide a code that will look at your data and copy the data from the rows above as you need. But it wouldn't insert the rows for you.

Ok. I will separate the rows manually and add them to separate sheets.

1 sheet will be needing 1 row added and the other sheet will need 2 rows added. Would that simplify? When I'm done, I could just combine them back into 1 sheet.

 

[Coyotex3]

Hmmm, any chance you can paste a sample of what your data looks like(omitting any personal information information)? Will 1 sheet be needing a row inserted after every row and the other sheet need 2 rows inserted after every row?

In the meantime since you mentioned there are 6 columns with the data, this code

Sub FillDown()
  With Range("A1:G" & Range("G" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

will get you from this:

PERSONAL.xlsm
	A	B	C	D	E	F	G	H
1	1	d	e	e	e	e	e
2
3	2	r	d	r	d	r	d
4
5	3	f	d	f	d	f	d
6
7	4	F	h	F	h	F	h
8
Sheet5

To this:

PERSONAL.xlsm
	A	B	C	D	E	F	G	H
1	1	d	e	e	e	e	e
2	1	d	e	e	e	e	e
3	2	r	d	r	d	r	d
4	2	r	d	r	d	r	d
5	3	f	d	f	d	f	d
6	3	f	d	f	d	f	d
7	4	F	h	F	h	F	h
8
Sheet5