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.

 

[DTMnBHM]

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

That looks perfect. And yes, one sheet will need 1 row added between. And the other will need 2

 

[Coyotex3]

That looks perfect. And yes, one sheet will need 1 row added between. And the other will need 2

Got it.

Give this code a try on a sample of your data after you have separated the data into 2 sheets. This code assumes that the worksheets are named Sheet1 and Sheet2 respectively.

Sub InsertRows()
Dim sht, sh1 As Worksheet
Dim i As Long
Dim LR As Long

Set sht = Worksheets("Sheet1")
Set sh1 = Worksheets("Sheet2")

LR = sht.Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 2 Step -1
    sht.Range("A1:A" & i).EntireRow(i).Insert
    
Next

LR = sh1.Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 2 Step -1
    sh1.Range("A1:A" & i).EntireRow(i).Resize(2).Insert
    
Next

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

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

This will result in

Sheet1:

PERSONAL.xlsm
	A	B	C	D	E	F	G
1	1	A	B	C	D	E	F
2	2	B	C	D	E	F	G
3	3	C	D	E	F	G	H
4	4	D	E	F	G	H	I
5	5	E	F	G	H	I	J
6	6	F	G	H	I	J	K
7	7	G	H	I	J	K	L
8	8	H	I	J	K	L	M
9	9	I	J	K	L	M	N
10	10	J	K	L	M	N	O
Sheet1

PERSONAL.xlsm
	A	B	C	D	E	F	G
1	1	A	B	C	D	E	F
2	1	A	B	C	D	E	F
3	2	B	C	D	E	F	G
4	2	B	C	D	E	F	G
5	3	C	D	E	F	G	H
6	3	C	D	E	F	G	H
7	4	D	E	F	G	H	I
8	4	D	E	F	G	H	I
9	5	E	F	G	H	I	J
10	5	E	F	G	H	I	J
11	6	F	G	H	I	J	K
12	6	F	G	H	I	J	K
13	7	G	H	I	J	K	L
14	7	G	H	I	J	K	L
15	8	H	I	J	K	L	M
16	8	H	I	J	K	L	M
17	9	I	J	K	L	M	N
18	9	I	J	K	L	M	N
19	10	J	K	L	M	N	O
Sheet1

Sheet2:

PERSONAL.xlsm
	A	B	C	D	E	F	G
1	11	A	B	C	D	E	F
2	12	B	C	D	E	F	G
3	13	C	D	E	F	G	H
4	14	D	E	F	G	H	I
5	15	E	F	G	H	I	J
6	16	F	G	H	I	J	K
7	17	G	H	I	J	K	L
8	18	H	I	J	K	L	M
9	19	I	J	K	L	M	N
10	20	J	K	L	M	N	O
11	21	A	B	C	D	E	F
12	22	B	C	D	E	F	G
13	23	C	D	E	F	G	H
14	24	D	E	F	G	H	I
15	25	E	F	G	H	I	J
16	26	F	G	H	I	J	K
17	27	G	H	I	J	K	L
18	28	H	I	J	K	L	M
19	29	I	J	K	L	M	N
20	30	J	K	L	M	N	O
21	31	A	B	C	D	E	F
22	32	B	C	D	E	F	G
23	33	C	D	E	F	G	H
24	34	D	E	F	G	H	I
25	35	E	F	G	H	I	J
26	36	F	G	H	I	J	K
27	37	G	H	I	J	K	L
28	38	H	I	J	K	L	M
29	39	I	J	K	L	M	N
30	40	J	K	L	M	N	O
Sheet2

PERSONAL.xlsm
	A	B	C	D	E	F	G
1	11	A	B	C	D	E	F
2	11	A	B	C	D	E	F
3	11	A	B	C	D	E	F
4	12	B	C	D	E	F	G
5	12	B	C	D	E	F	G
6	12	B	C	D	E	F	G
7	13	C	D	E	F	G	H
8	13	C	D	E	F	G	H
9	13	C	D	E	F	G	H
10	14	D	E	F	G	H	I
11	14	D	E	F	G	H	I
12	14	D	E	F	G	H	I
13	15	E	F	G	H	I	J
14	15	E	F	G	H	I	J
15	15	E	F	G	H	I	J
16	16	F	G	H	I	J	K
17	16	F	G	H	I	J	K
18	16	F	G	H	I	J	K
19	17	G	H	I	J	K	L
20	17	G	H	I	J	K	L
21	17	G	H	I	J	K	L
22	18	H	I	J	K	L	M
23	18	H	I	J	K	L	M
24	18	H	I	J	K	L	M
25	19	I	J	K	L	M	N
26	19	I	J	K	L	M	N
27	19	I	J	K	L	M	N
28	20	J	K	L	M	N	O
29	20	J	K	L	M	N	O
30	20	J	K	L	M	N	O
31	21	A	B	C	D	E	F
32	21	A	B	C	D	E	F
33	21	A	B	C	D	E	F
34	22	B	C	D	E	F	G
35	22	B	C	D	E	F	G
36	22	B	C	D	E	F	G
37	23	C	D	E	F	G	H
38	23	C	D	E	F	G	H
39	23	C	D	E	F	G	H
40	24	D	E	F	G	H	I
41	24	D	E	F	G	H	I
42	24	D	E	F	G	H	I
43	25	E	F	G	H	I	J
44	25	E	F	G	H	I	J
45	25	E	F	G	H	I	J
46	26	F	G	H	I	J	K
47	26	F	G	H	I	J	K
48	26	F	G	H	I	J	K
49	27	G	H	I	J	K	L
50	27	G	H	I	J	K	L
51	27	G	H	I	J	K	L
52	28	H	I	J	K	L	M
53	28	H	I	J	K	L	M
54	28	H	I	J	K	L	M
55	29	I	J	K	L	M	N
56	29	I	J	K	L	M	N
57	29	I	J	K	L	M	N
58	30	J	K	L	M	N	O
59	30	J	K	L	M	N	O
60	30	J	K	L	M	N	O
61	31	A	B	C	D	E	F
62	31	A	B	C	D	E	F
63	31	A	B	C	D	E	F
64	32	B	C	D	E	F	G
65	32	B	C	D	E	F	G
66	32	B	C	D	E	F	G
67	33	C	D	E	F	G	H
68	33	C	D	E	F	G	H
69	33	C	D	E	F	G	H
70	34	D	E	F	G	H	I
71	34	D	E	F	G	H	I
72	34	D	E	F	G	H	I
73	35	E	F	G	H	I	J
74	35	E	F	G	H	I	J
75	35	E	F	G	H	I	J
76	36	F	G	H	I	J	K
77	36	F	G	H	I	J	K
78	36	F	G	H	I	J	K
79	37	G	H	I	J	K	L
80	37	G	H	I	J	K	L
81	37	G	H	I	J	K	L
82	38	H	I	J	K	L	M
83	38	H	I	J	K	L	M
84	38	H	I	J	K	L	M
85	39	I	J	K	L	M	N
86	39	I	J	K	L	M	N
87	39	I	J	K	L	M	N
88	40	J	K	L	M	N	O
Sheet2

Please note that this code is not copying the last row of data. Unsure if you needed to copy that as well. Also, this is not the most efficient code out there as my knowledge is very limited. Depending on how much data you have, it might take a bit to run as inserting rows tend to slow things down in my experience. An alternative way of doing this might be with arrays.

Please let me know if this is a step in the right direction.

 

[DTMnBHM]

It looks like this worked on my small sample. I'm going to run it on the entire data set once I have my sheets correct. Thank you!

 

[Coyotex3]

It looks like this worked on my small sample. I'm going to run it on the entire data set once I have my sheets correct. Thank you!

Happy to help.