Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient than copying and pasting
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").NumberFo rmat = "0.00" Worksheets("Customer").Columns ("A:L").EntireColumn.AutoFit Worksheets("Customer").Protect DrawingObject = True, contents = True, Scenarios = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient than copying and pasting
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 Marques" wrote in message ... 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").NumberFo rmat = "0.00" Worksheets("Customer").Columns ("A:L").EntireColumn.AutoFit Worksheets("Customer").Protect DrawingObject = True, contents = True, Scenarios = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient than copying and pasting
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" wrote in message ... 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 -snip- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient than copying and pasting
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. -----Original Message----- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient than copying and pasting
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 "mark" wrote in message ... 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. -----Original Message----- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient than copying and pasting
Have you considered using a SQL data query to achieve this?
....queries work best when the 'database' book is closed :( keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Dale Marques" wrote: 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: |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient than copying and pasting
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 limit. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying and Pasting | Excel Discussion (Misc queries) | |||
Copying and Pasting | Excel Discussion (Misc queries) | |||
Copying and pasting ??? | Excel Discussion (Misc queries) | |||
Copying and Pasting | Excel Discussion (Misc queries) | |||
Copying & Pasting | Excel Programming |