Insert 2 blank cells and Fill it down.

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

How do I modify this script below (belongs to BabyTiger) to transform from this:

WORD:
abash
abess
abate

to this:

WORD:
abash
abash
abash
abess
abess
abess
abate
abate
abate
----------------
Range("A2").Select
Do While Range("A2").Offset(1, 0).Value <> ""
Range("A" & Range("A2").End(xlDown).Row & ":A" & Range("A2").End(xlDown).Row + 1).Insert Shift:=xlDown
Loop

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

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

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

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

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

(as per other thread.)

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

Thanks BabyTigerrrrrr!

Abdc · Mar 22nd, 2002, 01:31 AM

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
.Resize(, 2).Copy .Offset(c, 0).Resize(c + c, 2)
Rows("1:" & c * 3).Sort Key1:=Range("A1")
.EntireColumn.Delete
End With

(As per other thread)

Abdc · Mar 22nd, 2002, 01:50 AM

Just by way of mote :-

Whether you use a macro that uses a loop (like BabyTiger's) or a macro that avoids a loop (like mine) depends upon how much data you have to be processed and how important the process time is.

With 3000 lines of data, BabyToger's macro takes 34 seconds to run; the run time for the macro without a loop is not recordable (less than 1 second).

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

Mar 21st, 2002, 06:52 PM	#1
trillicomm Board Regular Join Date: Feb 2002 Posts: 95	How do I modify this script below (belongs to BabyTiger) to transform from this: WORD: abash abess abate to this: WORD: abash abash abash abess abess abess abate abate abate ---------------- Range("A2").Select Do While Range("A2").Offset(1, 0).Value <> "" Range("A" & Range("A2").End(xlDown).Row & ":A" & Range("A2").End(xlDown).Row + 1).Insert Shift:=xlDown Loop ----------------

Mar 21st, 2002, 07:04 PM	#2
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	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

Mar 21st, 2002, 07:21 PM	#4
trillicomm Board Regular Join Date: Feb 2002 Posts: 95	Thanks BabyTigerrrrrr!

Mar 22nd, 2002, 01:31 AM	#5
Abdc Join Date: Mar 2002 Posts: 20	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 .Resize(, 2).Copy .Offset(c, 0).Resize(c + c, 2) Rows("1:" & c * 3).Sort Key1:=Range("A1") .EntireColumn.Delete End With (As per other thread)

Mar 22nd, 2002, 01:50 AM	#6
Abdc Join Date: Mar 2002 Posts: 20	Just by way of mote :- Whether you use a macro that uses a loop (like BabyTiger's) or a macro that avoids a loop (like mine) depends upon how much data you have to be processed and how important the process time is. With 3000 lines of data, BabyToger's macro takes 34 seconds to run; the run time for the macro without a loop is not recordable (less than 1 second).