Separating Rows

J

Jeno

I have a spreadsheet with one column with over 3000 numbers. I'd like to add
a blank row after twenty five numbers, another blank row after the next
twenty five, etc all the way down. I know that a macro can be used, but I
don't know that much about writing them. Anyone able to easily help me with
the macro or another solution? Thank you in advance for your expertise.
Happy New Year.
 
D

Don Guillett

try
Sub insertrowsat25()
lr = Cells(Rows.Count, "a").End(xlUp).Row
MsgBox lr
For i = 25 To lr Step 25
Rows(i).Insert
Next i
End Sub
 
B

Bob Umlas

After the setup, this will be fairly instantaneous, & no macros.
Assuming your data goes from A1:N3000, enter this in O1 (first available
column):
=INT((ROW()+24)/25)
Then fill it all the way down (if the col to the left hae no blanks,
double-click the fill handle). This will give you 25 1's, 25 2's, 25 3's,
etc.
If it goes to row 3000, then the last number will be 120.
Below this, in O3001, for example, enter 1
IN the same cell, use Edit/Fill/Series. Change to Columns (default is rows),
enter 120 as the stop value.
Now, select everything, and sort by column O. You're done!

Bob Umlas
Excel MVP

I'm leading a FREE 1-hour online Webinar on Excel Tips & Tricks on Feb 13
and Feb 26 from 6-7PM est. If interested,
go to http://www.iil.com, click on the yellow/orange "Try a free webinar"
link
on the left side, click the Microsoft Excel Tips & Tricks link,
follow instructions to register.

You can also order my book on tips & tricks by visiting the site
http://www.iil.com/iil/excelmagic
Thanks.
Bob Umlas
 
Top