insert

[Rajat Soni]

hello all
I have a column full of data now I want to insert two blank cells in
my column below each cell so that I have two blank cells below every
filled data, please tell me a shortcut as the data is very big and
inserting manually is very tedious.

 

[Simon Lloyd]

Hi, it can be done smarter than this, but run this in a standard module
dont forget to change any reference to "A" to your actual column

Code
-------------------
Sub insertcells(
Dim i As Long, ic As Lon
i = Range("A" & Rows.Count).End(xlUp).Ro
For ic = i To 2 Step -
With Cells(ic, "A"
.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbov
.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbov
End Wit
Next i
End Su

-------------------

hello al
I have a column full of data now I want to insert two blank cells i
my column below each cell so that I have two blank cells below ever
filled data, please tell me a shortcut as the data is very big an
inserting manually is very tedious

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Lars-Åke Aspelin]

hello all
I have a column full of data now I want to insert two blank cells in
my column below each cell so that I have two blank cells below every
filled data, please tell me a shortcut as the data is very big and
inserting manually is very tedious.

If you don't want to use a macro you may try this one:

Assuming your data is in column A starting on row 1,
put this formula in the first row of a free helper column:

=IF(MOD(ROW(),3)=1,OFFSET(A$1,INT(ROW()/3),,),"")

Copy the formula down as far as needed, then copy the column
and "Paste Special/Values" it onto column A. Finally clear the helper
column.

Hope this helps / Lars-Åke

 

[Ashish Mathur]

Hi,

You could also try this. Suppose your data is in range B5:B55. In range
C5"C55, type values from 1-51. Now copy C5:C55 and paste it in C56:C106 and
then in rangeC107:C157.

Now select B5:C157 and sort (in ascending order) on column C.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com