Excel VBA autofill by "x" number of rows.

[stereoqt]

Hello! I want to automate some changes I have to make to these tables regularly.

Basically, I need to copy down (autofill) the formulas in columns E4, F4 and G4 (same as M4, N4, O4) the number of times (value) wrote in B1 (J1).

For example, if I write 1 in B1, there should only be 1 column. If I write 2, two columns, etc.

The formula in D4 works differently, it is copied according to the number of columns in the table on the left. (Check L4:L14)

I managed to do it using the following code, but I have no idea about the other formulas.

Sub test()

    Dim lastRow As Long
    lastRow = Range("C" & Rows.Count).End(xlUp).Row
    Range("D2").AutoFill Destination:=Range("D2:D" & lastRow)

End Sub

Test1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Number	1							Number	2
2
3	Index	Count							Index	Count
4	1	51095025		0.25	1	1	CatItem254319		4	7453239		0.21	10	10	CatItem21230
5	2	26978165							5	6998459		0.15	12	12	CatItem12987
6	3	13148406							6	5727455		0.07
7									7	5331643		0.87
8									8	3821481		0.22
9									9	2610562		0.63
10									10	2427388		0.01
11									11	2268117		0.91
12									12	2056462		0.28
13									13	1802451		0.19
14									14	1045924		0.41
15
Filtered1

Cell Formulas
Range	Formula
D4,L4:L14	D4	=RAND()
E4	E4	=INDEX($A$4:$A$6,RANK.EQ(D4,$D$4:$D$6)+COUNTIF($D$4:D4,D4)-1,1)
F4	F4	=SMALL($E$4:$E$6,ROW(A1))
G4,O4:O5	G4	=VLOOKUP(F4,Database,2)
M4:M5	M4	=INDEX($I$4:$I$14,RANK.EQ(L4,$L$4:$L$14)+COUNTIF($L$4:L4,L4)-1,1)
N4:N5	N4	=SMALL($M$4:$M$14,ROW(A1))

 

[Anthony47]

Maybe

'...
Range("E4:G4").AutoFill Range("E4").Resize(1 + Range("B1").Value, 3)
'...
Range("M4:O4").AutoFill Range("M4").Resize(1 + Range("J1").Value, 3)
'...

Bye

 

[stereoqt]

Maybe

'...
Range("E4:G4").AutoFill Range("E4").Resize(1 + Range("B1").Value, 3)
'...
Range("M4:O4").AutoFill Range("M4").Resize(1 + Range("J1").Value, 3)
'...

Bye

Hello Anthony, first of all, thank you for responding. I couldn't make it work, however, I achieved the result I wanted using the following code:

Sub autofill()

    Dim Numbert1 As Long
    Dim Numbert2 As Long
   
    Dim lrC As Long
    Dim lrD As Long
    Dim lrE As Long
    Dim lrF As Long
   
    Dim lrJ As Long
    Dim lrK As Long
    Dim lrL As Long
    Dim lrM As Long
   
   
    Numbert1 = Range("A1").Value
    Numbert2 = Range("H1").Value

   
    lrC = Range("A" & Rows.Count).End(xlUp).Row
    lrD = Range("D" & (3 + Numbert1)).Row
    lrE = Range("E" & (3 + Numbert1)).Row
    lrF = Range("F" & (3 + Numbert1)).Row
   
    lrJ = Range("H" & Rows.Count).End(xlUp).Row
    lrK = Range("K" & (3 + Numbert2)).Row
    lrL = Range("L" & (3 + Numbert2)).Row
    lrM = Range("M" & (3 + Numbert2)).Row

   
    Range("C3").autofill Range("C3:C" & lrC)
    Range("D3").autofill Range("D3:D" & lrD)
    Range("E3").autofill Range("E3:E" & lrE)
    Range("F3").autofill Range("F3:F" & lrF)
   
    Range("J3").autofill Range("J3:J" & lrJ)
    Range("K3").autofill Range("K3:K" & lrK)
    Range("L3").autofill Range("L3:L" & lrL)
    Range("M3").autofill Range("M3:M" & lrM)
   
     
End Sub

In cells A1 and H1 I write the number of rows I want to add, but every time I write 0, I get an error (I can't autofill the same row I guess).

I think that implementing an If block in the code would do the trick

If Numbert1 = 0
Then (Do nothing)

But I have no idea how to do it since my VBA skills are quite limited. Can anyone help me?

test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	0							4
2	Number	Count	Rand	Index	Min	Vlookup		Number	Count	Rand	Index	Min	Vlookup
3	1	51095025	0.6	1	1	CatItem254319		4	7453239	0.77	9	4	CatItem12402
4	2	26978165	0.14	3	3	CatItem87440		5	6998459	0.97	4	5	CatItem 43341
5	3	13148406	0.17					6	5727455	0.87	7	7	CatItem90134
6								7	5331643	0.21	13	9	CatItem20981
7								8	3821481	0.93	5	13	CatItem19620
8								9	2610562	0.93
9								10	2427388	0.2
10								11	2268117	0.62
11								12	2056462	0.84
12								13	1802451	0.57
13								14	1045924	0.69
14
Sheet1

Cell Formulas
Range	Formula
D3:D4	D3	=INDEX([Number],RANK.EQ(C3,[Rand])+COUNTIF($C$3:C3,C3)-1,1)
E3:E4	E3	=SMALL([Index],ROW(A1))
M3:M7,F3:F4	F3	=VLOOKUP(E3,Database,2)
K3:K7	K3	=INDEX([Number],RANK.EQ(J3,[Rand])+COUNTIF($J$3:J3,J3)-1,1)
L3:L7	L3	=SMALL([Index],ROW(A1))
J3:J13,C3:C5	C3	=RAND()

 

[Anthony47]

Try this version:

Sub Autofill()
Dim Numbert1 As Long
Dim Numbert2 As Long
Dim lrC As Long
Dim lrJ As Long
'   
Numbert1 = Range("A1").Value
Numbert2 = Range("H1").Value
'
lrC = Range("A" & Rows.Count).End(xlUp).Row
lrJ = Range("H" & Rows.Count).End(xlUp).Row
If lrC > 3 Then
    Range("C3").Autofill Range("C3:C" & lrC)
End If
If Numbert1 > 1 Then
    Range("D3").Autofill Range("D3:D" & 3 + Numbert1)
    Range("E3").Autofill Range("E3:E" & 3 + Numbert1)
    Range("F3").Autofill Range("F3:F" & 3 + Numbert1)
End If
If lrJ > 3 Then
    Range("J3").Autofill Range("J3:J" & lrJ)
End If
If Numbert2 > 1 Then
    Range("K3").Autofill Range("K3:K" & 3 + Numbert2)
    Range("L3").Autofill Range("L3:L" & 3 + Numbert2)
    Range("M3").Autofill Range("M3:M" & 3 + Numbert2)
End If
End Sub

Bye