More efficient than copying and pasting

  • Thread starter Thread starter Dale Marques
  • Start date Start date
D

Dale Marques

I wrote a program that calculates loads for an electric
company. Then it takes the loads that are greater than a
certain number and less than a certain number and puts
them into different sheets. Everything works fine, but
there is 22000+ rows of data and when I copying and paste
everything it is very slow, I was wondering if there was a
more efficient method of doing this. Here is the code
that I wrote:

Sub HighLoad()
' HighLoadFactor Macro
' Macro recorded 6/15/2004 by Dale D. Marques
' Declaring and Setting Variables
Worksheets("HighLoadFactor").Unprotect
Dim rng2 As Range
Set rng2 = Worksheets("Customer").Range("L7:L23000")
Worksheets("HighLoadFactor").Range
("A7:L23000").ClearContents
Worksheets("HighLoadFactor").Activate
Range("A7").Select
' For Loop High Load Factor
For a = 1 To rng2.Cells.Count
If rng2.Cells(a).Value >= 0.9 Then
Worksheets("Customer").Range("A7:L7").Rows
(a).Copy
Worksheets("HighLoadFactor").Activate
Worksheets("HighLoadFactor").PasteSpecial
ActiveCell.Offset(rowoffset:=1).Select
End If
Next a
' Formatting Columns, Numbers, and Application
With Worksheets("HighLoadFactor").Columns("L")
.NumberFormat = "0.00"
.EntireColumn.AutoFit
End With
Worksheets("HighLoadFactor").Columns
("A:K").EntireColumn.AutoFit
Application.CutCopyMode = False
Range("A7:L23000").Sort Key1:=Range("L7"),
Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Worksheets("HighLoadFactor").Protect DrawingObjects =
True, contents = True, Scenarios = True
End Sub


Sub LowLoad()
' LowLoadfactor Macro
' Macro recorded 6/15/2004 by Dale D. Marques
' Declaring and Setting Variables
Worksheets("LowLoadFactor").Unprotect
Dim rng1 As Range
Set rng1 = Worksheets("Customer").Range("L7:L23000")
Worksheets("LowLoadFactor").Range
("A7:L23000").ClearContents
Worksheets("LowLoadFactor").Activate
Range("A7").Select
' For Loop Low Load Factor
For i = 1 To rng1.Cells.Count
If rng1.Cells(i).Value <= 0.1 And rng1.Cells(i)
<> "" Then
Worksheets("Customer").Range("A7:L7").Rows
(i).Copy
Worksheets("LowLoadFactor").Activate
Worksheets("LowLoadFactor").PasteSpecial
ActiveCell.Offset(rowoffset:=1,
columnoffset:=0).Select
End If
Next i
' Formatting Columns, Numbers, and Application
With Worksheets("LowLoadFactor").Columns("L")
.NumberFormat = "0.00"
.EntireColumn.AutoFit
End With
Worksheets("LowLoadfactor").Columns
("A:K").EntireColumn.AutoFit
Application.CutCopyMode = False
Range("A7:L23000").Sort Key1:=Range("L7"),
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Worksheets("LowLoadFactor").Protect DrawingObjects =
True, contents = True, Scenarios = True
End Sub

Sub LoadFactor()
' LoadFactorEquation Macro
' Macro recorded 6/16/2004 by Dale D. Marques
' Declaring and Setting Variables
Worksheets("Customer").Unprotect
Dim kWh As Range
Dim Demand As Range
Dim Days As Range
Dim Factor As Range
Set kWh = Worksheets("Customer").Range("G7:G23000")
Set Demand = Worksheets("Customer").Range("H7:H23000")
Set Days = Worksheets("Customer").Range("K7:K23000")
Set Factor = Worksheets("Customer").Range("L7:L23000")
Factor.ClearContents
' For Loop for Calculation
For i = 1 To kWh.Cells.Count
If kWh.Cells(i) <= 0 Then
Factor.Cells(i) = "0.00"
End If
If kWh.Cells(i) <> "" And Demand.Cells(i) <> 0 And
Days.Cells(i) <> 0 Then
LF = Round(kWh.Cells(i) / (Demand.Cells(i) *
Days.Cells(i) * 24), 2)
Factor.Cells(i) = LF
End If
If Demand.Cells(i) = 0 And Demand.Cells(i) <> ""
Then
Factor.Cells(i) = "0.00"
End If
If kWh.Cells(i) = "" And Demand.Cells(i) = "" And
Days.Cells(i) = "" Then
Factor.Cells(i) = ""
End If
If Days.Cells(i) = 0 And Days.Cells(i) <> "" Then
Factor.Cells(i) = "0.00"
End If
Next i
' Formatting Columns and Application
Worksheets("Customer").Range("L7:L23000").NumberFormat
= "0.00"
Worksheets("Customer").Columns
("A:L").EntireColumn.AutoFit
Worksheets("Customer").Protect DrawingObject = True,
contents = True, Scenarios = True
End Sub
 
In general you do not need to select-copy-paste and your select / activate
statements will slow things down.

Try using a loop to control the destination row increments and assign the
values directly to the destination, you might also consider using one pass
of the data to cover both the low and high values.

Example how to copy to a destination without selecting sheets etc..

Sheets("Sheet1").Range("A2:H15").Copy
Sheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteValues

The loop might look like this......you'll need to change the references to
suit your data etc....

Dim ir As Long, irlow As Long, irhigh As Long
irlow = 1 'first low value destination row
irhigh = 1 ' first high values destination row
Sheets("Data").Activate
For ir = 1 To 200
Select Case Cells(ir, 1).Value
Case Is >= 0.9
Sheets("Data").Range(Cells(ir, 1), Cells(ir, 12)).Copy
Sheets("High").Cells(irhigh, 1).PasteSpecial Paste:=xlPasteValues
irhigh = irhigh + 1
Case Is <= 0.1
Sheets("Data").Range(Cells(ir, 1), Cells(ir, 12)).Copy
Sheets("Low").Cells(irlow, 1).PasteSpecial Paste:=xlPasteValues
irlow = irlow + 1
End Select
Next ir
Application.CutCopyMode = False


HTH
Cheers
Nigel
 
Dale,

I've made quite a few changes.
The major change is the elimination of the copy and paste.
The values on one sheet are made equal to the other.
The loop was changed from For/Next to For Each/Next.
Also, you need to add Option Explicit at the very top of the module _
that will flag the undeclared variables in the subs.
Only the first sub was changed, you can do the work on the others...
I've only done very limited testing. Please let us know how it works for you.

'---------------------------------------------------------------------
Sub HighLoad()
' HighLoadFactor Macro
' Macro recorded 6/15/2004 by Dale D. Marques
' Modified by Jim Cone 06/25/2004
' Enters values from the Customer sheet into the HighLoadFactor sheet.

' Declaring and Setting Variables
Dim CustRng As Range
Dim objCell As Range
Dim ShtHLF As Worksheet

Set CustRng = Worksheets("Customer").Range("L7:L23000")
Set ShtHLF = Worksheets("HighLoadFactor")

ShtHLF.Select
ShtHLF.Unprotect
ShtHLF.Range("A7:L23000").ClearContents

' For Loop High Load Factor
For Each objCell In CustRng
If objCell.Value >= 0.9 Then
ShtHLF.Rows(objCell.Row).Value = objCell.EntireRow.Value
End If
Next 'objCell

' Formatting Columns, Numbers, and Application
ShtHLF.Columns("L").NumberFormat = "0.00"
ShtHLF.Columns("A:L").EntireColumn.AutoFit
ShtHLF.Range("A7:L23000").Sort Key1:=ShtHLF.Range("L7"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ShtHLF.Protect DrawingObjects:=True, contents:=True, Scenarios:=True 'JBC ":" added

Set objCell = Nothing
Set CustRng = Nothing
Set ShtHLF = Nothing
End Sub
'----------------------------------------------

Regards,
Jim Cone
San Francisco, CA

Dale Marques said:
I wrote a program that calculates loads for an electric
company. Then it takes the loads that are greater than a
certain number and less than a certain number and puts
them into different sheets. Everything works fine, but
there is 22000+ rows of data and when I copying and paste
everything it is very slow, I was wondering if there was a
more efficient method of doing this. Here is the code
that I wrote:

Sub HighLoad()
' HighLoadFactor Macro
' Macro recorded 6/15/2004 by Dale D. Marques
' Declaring and Setting Variables
Worksheets("HighLoadFactor").Unprotect
Dim rng2 As Range
Set rng2 = Worksheets("Customer").Range("L7:L23000")
Worksheets("HighLoadFactor").Range
("A7:L23000").ClearContents
Worksheets("HighLoadFactor").Activate
Range("A7").Select
' For Loop High Load Factor
For a = 1 To rng2.Cells.Count
If rng2.Cells(a).Value >= 0.9 Then
Worksheets("Customer").Range("A7:L7").Rows
(a).Copy
Worksheets("HighLoadFactor").Activate
Worksheets("HighLoadFactor").PasteSpecial
ActiveCell.Offset(rowoffset:=1).Select
End If
Next a
' Formatting Columns, Numbers, and Application
With Worksheets("HighLoadFactor").Columns("L")
.NumberFormat = "0.00"
.EntireColumn.AutoFit
End With
Worksheets("HighLoadFactor").Columns
("A:K").EntireColumn.AutoFit
Application.CutCopyMode = False
Range("A7:L23000").Sort Key1:=Range("L7"),
Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Worksheets("HighLoadFactor").Protect DrawingObjects =
True, contents = True, Scenarios = True
End Sub
Click to expand...

-snip-
 
In general, the looping through 22k will just be slow,
anyway.

Another approach which might help would be to copy the
entire chunk of data in one step, and then use the results
of a Boolean formula to identify and delete the undersired
rows all in one pass.

sort of like this:

1) Copy ALL data to new sheet
2) Enter formula to determine if you want to keep it

=if(condition,"",1)

3) Sort the entire working data range by the "" or 1
column,

4) Select the number of rows that you want to delete
5) Delete the rows...

Spreadsheets process their predefine formulas really
quickly, and it won't take long at all to evaluate the
formula for 22k rows,

copy bulk
evarluate formula
sort
delete bulk.

If you need help on any specifics, post again.
 
Good point. Might be worth considering sorting the data from highest to
lowest values and then reading and copying the data until the value falls
below the limit, then sort in reverse order (low to high) and do the same
reading and copying until the value exceeds the lower limit.

I guess the only challenge is if there are a significant number of outliers
and of course your source is now in a new order, but you could add a
sequential key to each row before sorting then use the key to put the file
back in order. The benefit of the later approach is that if the key is
copied across to the high and low sheets the key links the data to the
source and provides a means to sort the sheets into the original order as
well.

Cheers
Nigel
 
Yep. Your suggestion would work, too... the efficiency
lies in how many outliers there are.

Personally, I just have a 'del_rows' subroutine in my
callable utilities module... just call the subroutine with
whatever the current Boolean formula is, and the
subroutine goes through deleting any rows that meet that
criteria.

Because of the way Excel sorts work, most often, the data
comes back in the same sort order as it was originally,
just missing the deleted data, as desired.

Sometimes, if there's something else involved, I put in an
index as you mentioned... and yes, that would be a good
way to identify which line in the original sample that the
row came from.

-----Original Message-----
Good point. Might be worth considering sorting the data from highest to
lowest values and then reading and copying the data until the value falls
below the limit, then sort in reverse order (low to high) and do the same
reading and copying until the value exceeds the lower
Click to expand...
limit.
 
Back
Top