Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
insert
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
insert
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 Long i = Range("A" & Rows.Count).End(xlUp).Row For ic = i To 2 Step -1 With Cells(ic, "A") .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End With Next ic End Sub -------------------- Rajat Soni;420326 Wrote: 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 Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116964 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
insert
On Thu, 16 Jul 2009 23:25:17 -0700 (PDT), Rajat Soni
wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
insert
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 "Rajat Soni" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot insert worksheet in exel - not available in insert menu | Excel Worksheet Functions | |||
insert row / insert column command buttons | Excel Worksheet Functions | |||
How can I insert a date with an icon (calendar) insert | Excel Discussion (Misc queries) | |||
Can I auto insert a worksheet when I insert a value in a cell. | Excel Worksheet Functions | |||
Insert Next? Or insert a variable number of records...how? | Excel Discussion (Misc queries) |