PDA

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



Simon Lloyd
11-09-2007, 06:57 PM
Hi all, does anyone have any tips for performing the task below any smarter?

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

Paul_Hossler
11-09-2007, 10:05 PM
Well, I don't know if it's smarter, but it is a different way :think:

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



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

Bob Phillips
11-10-2007, 04:31 AM
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

Paul_Hossler
11-11-2007, 07:26 AM
XLD --

Your's is simpler and more better :clap:

Simon Lloyd
11-11-2007, 01:06 PM
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
11-11-2007, 04:14 PM
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


Range("D" & LastRow + 1).Resize(LastRow)


I could have used


Range("D" & LastRow + 1 & ":D" & LastRow + LastRow)


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

Simon Lloyd
11-12-2007, 05:40 AM
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
11-12-2007, 05:42 AM
Not sure what you mean.

Simon Lloyd
11-12-2007, 05:48 AM
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
11-12-2007, 06:24 AM
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
11-12-2007, 06:29 AM
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!