Help: how to insert 2 blank cells in between the rows until

trillicomm · Mar 21st, 2002, 04:02 PM

WORD
abase
abash
abbess
abbey
abbot
abdicate
.
.
.
Thank you in advance!

BabyTiger · Mar 21st, 2002, 04:54 PM

Hi,

You might want to try the following:

Range("L20").Select
Do While Range("L20").Offset(1, 0).Value <> ""
Range("L" & Range("L20").End(xlDown).Row & ":L" & Range("L20").End(xlDown).Row + 1).Insert Shift:=xlDown
Loop

It is working from bottom up, and the above would only work if you data column if continuous (ie. no empty cells in between). And "L20" is where the start cell is.

And if you have data in the other cells/columns, and you want to shift whem down as well, then use:

Range("L" & Range("L20").End(xlDown).Row & ":L" & Range("L20").End(xlDown).Row + 1).EntireRow.Insert

instead of the line above.

Hope it helps

Abdc · Mar 21st, 2002, 05:24 PM

Alternatively :-

Dim rng As Range, c As Long
Application.ScreenUpdating = False
Columns(1).Insert
Set rng = Range([B1], [B65536].End(xlUp)).Offset(0, -1)
c = rng.Cells.Count
With rng
.Cells(1, 1).Value = 1
.Cells(1, 1).AutoFill Destination:=rng, Type:=xlFillSeries
.Copy .Offset(c, 0).Resize(c + c, 1)
Rows("1:" & c * 3).Sort Key1:=Range("A1")
.EntireColumn.Delete
End With

[ This Message was edited by: Abdc on 2002-03-21 16:25 ]

trillicomm · Mar 21st, 2002, 06:21 PM

Thank you.

trillicomm · Mar 21st, 2002, 06:37 PM

eom

[ This Message was edited by: trillicomm on 2002-03-21 17:52 ]

BabyTiger · Mar 21st, 2002, 06:59 PM

Try the following, and see where that's what you need:

Range("A2").Select
Row_Numb = Range("A2").End(xlDown).Row
For i = Row_Numb To 2 Step -1
Rows(i).Copy
Rows(i & ":" & i + 1).Insert shift:=xlDown
Next i

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 04:02 PM	#1
trillicomm Board Regular Join Date: Feb 2002 Posts: 95	WORD abase abash abbess abbey abbot abdicate . . . Thank you in advance!

Mar 21st, 2002, 04:54 PM	#2
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	Hi, You might want to try the following: Range("L20").Select Do While Range("L20").Offset(1, 0).Value <> "" Range("L" & Range("L20").End(xlDown).Row & ":L" & Range("L20").End(xlDown).Row + 1).Insert Shift:=xlDown Loop It is working from bottom up, and the above would only work if you data column if continuous (ie. no empty cells in between). And "L20" is where the start cell is. And if you have data in the other cells/columns, and you want to shift whem down as well, then use: Range("L" & Range("L20").End(xlDown).Row & ":L" & Range("L20").End(xlDown).Row + 1).EntireRow.Insert instead of the line above. Hope it helps

Mar 21st, 2002, 05:24 PM	#3
Abdc Join Date: Mar 2002 Posts: 20	Alternatively :- Dim rng As Range, c As Long Application.ScreenUpdating = False Columns(1).Insert Set rng = Range([B1], [B65536].End(xlUp)).Offset(0, -1) c = rng.Cells.Count With rng .Cells(1, 1).Value = 1 .Cells(1, 1).AutoFill Destination:=rng, Type:=xlFillSeries .Copy .Offset(c, 0).Resize(c + c, 1) Rows("1:" & c * 3).Sort Key1:=Range("A1") .EntireColumn.Delete End With [ This Message was edited by: Abdc on 2002-03-21 16:25 ]

Mar 21st, 2002, 06:21 PM	#4
trillicomm Board Regular Join Date: Feb 2002 Posts: 95	Thank you.

Mar 21st, 2002, 06:37 PM	#5
trillicomm Board Regular Join Date: Feb 2002 Posts: 95	eom [ This Message was edited by: trillicomm on 2002-03-21 17:52 ]

Mar 21st, 2002, 06:59 PM	#6
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	Try the following, and see where that's what you need: Range("A2").Select Row_Numb = Range("A2").End(xlDown).Row For i = Row_Numb To 2 Step -1 Rows(i).Copy Rows(i & ":" & i + 1).Insert shift:=xlDown Next i