Solved: Tips on a smarter way to perform Insert Line & Paste info?

[Simon Lloyd]

Hi all, does anyone have any tips for performing the task below any smarter?
[VBA]
Sub Split_Reg_OT()
Dim Rrow As Integer
Dim lastrow As Long
lastrow = Range("A65536").End(xlUp).Row
For Rrow = lastrow + 1 To 3 Step -1
With Rows(Rrow)
.Insert Shift:=xlDown
Range("A" & Rrow).Offset(-1, 0).Copy Destination:=Range("A" & Rrow)
Range("A" & Rrow).Offset(-1, 1).Copy Destination:=Range("B" & Rrow)
Range("A" & Rrow).Offset(-1, 3).Copy Destination:=Range("D" & Rrow)
Range("A" & Rrow).Offset(-1, 3).ClearContents
End With
Next Rrow
Application.CutCopyMode = False

End Sub
[/VBA]

[Paul_Hossler]

Well, I don't know if it's smarter, but it is a different way

I always like to use the intrinsic Excel/VBA object methods, instead of For/Next loops to up and down rows. While it might make the code a little more abstact, IMHO it improves performance.

Basicly this code adds the row number to the end, copies the data, clears the Reg from the original range and the OT from the copies, re-sorts by the row marker, and then cleans up

As far as I can tell, without having any of your real data, it gives equivalent results

Paul


[VBA]
Sub Demo()

Dim rOrig As Range, rDest As Range

Application.ScreenUpdating = False

With ActiveSheet.Cells(1, 1).CurrentRegion
With .Parent.Cells(1, .Columns.Count + 1).Resize(.Rows.Count, 1)
.Formula = "=ROW()"
Calculate
.Copy
.PasteSpecial (xlValues)
End With
End With

With ActiveSheet.Cells(1, 1).CurrentRegion
Set rOrig = .Cells(2, 1).Resize(.Rows.Count - 1, .Columns.Count)
Set rDest = .Cells(2, 1).End(xlDown).Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
End With

Call rOrig.Copy(rDest)

rOrig.Columns(3).Clear
rDest.Columns(4).Clear

ActiveSheet.Cells(1, 1).CurrentRegion.Sort Key1:=Range("E2"), Order1:=xlAscending, _
Key2:=Range("C2"), Order2:=xlAscending, Header:=xlYes

With ActiveSheet.Cells(1, 1).CurrentRegion
.Columns(.Columns.Count).Clear
End With

End Sub
[/VBA]

[Bob Phillips]

[vba]

Sub Split_Reg_OT2()
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Columns(1).Insert
Range("A1").Value = 1
Range("A2").Value = 2
Range("A1:A2").AutoFill Range("A1").Resize(LastRow)
Range("A1").Resize(LastRow, 5).Copy Cells(LastRow + 1, "A")
Range("E1").Resize(LastRow).ClearContents
Range("D" & LastRow + 1).Resize(LastRow).ClearContents
Cells.Sort Key1:=Range("A1"), header:=xlNo
Columns(1).Delete
End Sub
[/vba]

[Paul_Hossler]

XLD --

Your's is simpler and more better

[Simon Lloyd]

Thanks for the reply both, the code i posted is one i posted to an Op on another forum...it gets the job done but if i can do it that way others can do it smarter, you both use Resize can you explain how this function works, i've been away from VBA for quite a while as i havent had a pc for the last 5 months as my laptop went up in smoke!, however i have shared use of a desktop for a while until i can afford another laptop....it's good to be back in whatever sense!

[Bob Phillips]

It's good to see you back Simon. I had assumed that work was getting in the way!

Resize is simply used to resize a range by the number of rows and columns.

Where I used

[vba]
Range("D" & LastRow + 1).Resize(LastRow)
[/vba]

I could have used

[vba]
Range("D" & LastRow + 1 & "" & LastRow + LastRow)
[/vba]

but I tink you will agree that Resize is more elegant.

[Simon Lloyd]

Bob, it certainly is a smart way of doing it and i can see that advantages over a loop for a large amount of data (provided that the user hasn't used more than 32768 rows!), would the method affect formulae either on the activesheet or other sheets that look at areference on the activesheet?

[Bob Phillips]

Not sure what you mean.

[Simon Lloyd]

Just my usual tact of playing devils advocat, sometimes if i have formulae on another sheet that reference the activesheet that i add a column to then subsequently delete said column the formulae all show #REF! of course it may be my ineptitude in applying formulae in the first place but i assume other folk that i help may have the same failings, also what if they would like to perform the task on more than 32768 rows ?(for those of us who don't have and probably wont have Excel 2007)

[Bob Phillips]

Shouldn't be a problem, the problem usually arises when deleting a column/row that is referenced. Just autofilling should be fine.

32768 is not a limit unless you use an integer variable rather than a long one (but nobody does that anymore, right?).

[Simon Lloyd]

Sorry Bob, i was just looking at how the Resize worked by adding a column and practically duplicating all the data before sorting it back to its original size, so the way it looked to me was that if we used over half the rows available that method would not work...of course i know that we could AddItem to a collection and probably use those collections but then again we wouldn't be using Resize...would we? you have to forgive me seeming a bit dim Bob!

That's 'cos i am!