how to spread data evenly in multiple rows

[zgsharon]

I found this code online and was wondering if someone could tell me how to make this work for more than one column at once? The code inserts a specific number of blank spaces into a column of data. I just need to do it for 3 or 4 columns at once and am having some problems altering the code. Any help would be greatly appreciated.

Sub MG28Sep05()
Dim Last As Integer, n
' Change "A" Below To another column Letter
Last = Range("A" & Rows.Count).End(xlUp).row

For n = Last To 2 Step -1
' Change "7" Below To No of Blank Rows Required
Cells(n, 1).Resize(7).Insert shift:=xlDown
Next n
End Sub

 

[DKaur]

Change Resize(7) to Resize(7,3) or Resize(7,4) depending on how many columns you need it for. Should work.

 

[zgsharon]

Thank you DKaur. That worked like a charm. Do you know if there is a way to get the data to spread a random value of rows over a given range? So in some cases there might be 4 blank rows in between numbers and in another case there would only be 2 or 3. If I start with the data below and run the code, I'd want it to look like the second example where the start and end values of columns A, B and C match those of D, E and F with a random value of blank rows placed in between.
[TABLE="class: cms_table, width: 448"]
<tbody>[TR]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[/TR]
[TR]
[TD="width: 64"]6[/TD]
[TD="width: 64"]6[/TD]
[TD="width: 64"]6[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]5[/TD]
[TD="width: 64"]5[/TD]
[TD="width: 64"]4[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD][/TD]
[TD]64[/TD]
[TD]45[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]43[/TD]
[TD]4[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]234[/TD]
[TD]23[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]64[/TD]
[TD]46[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]234[/TD]
[TD]64[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]24[/TD]
[TD]2[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]34[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]56[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]23[/TD]
[TD]32[/TD]
[/TR]
</tbody>[/TABLE]

After running code- I just need the first and last rows of columns A, B and C to line up with D, E, and F, with random blanks placed in between. So there are 3 blank spaces and then only two after the row of 4's.

[TABLE="class: cms_table, width: 448"]
<tbody>[TR]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[/TR]
[TR]
[TD="width: 64"]6[/TD]
[TD="width: 64"]6[/TD]
[TD="width: 64"]6[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]5[/TD]
[TD="width: 64"]5[/TD]
[TD="width: 64"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]64[/TD]
[TD]45[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]43[/TD]
[TD]4[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]234[/TD]
[TD]23[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD][/TD]
[TD]64[/TD]
[TD]46[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]234[/TD]
[TD]64[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]24[/TD]
[TD]2[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]34[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]56[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]23[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]23[/TD]
[TD]4[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

 

[DKaur]

How about this:

Sub MG28Sep05()
Dim Last, EndRow, n, y As Integer
' Change "A" Below To another column Letter
Last = Range("A" & Rows.Count).End(xlUp).Row
EndRow = Range("D" & Rows.Count).End(xlUp).Row


For n = Last To 2 Step -1
If n = Last Then
Cells(n, 1).Resize(EndRow - Last, 3).Insert shift:=xlDown
Else
Cells(n, 1).Resize(1, 3).Cut
y = n * ((EndRow) / (Last))
Cells(y, 1).Select
ActiveSheet.Paste
End If
Next n


End Sub