PDA

View Full Version : How can i speed this up?



AlexMason
10-29-2009, 02:03 AM
heres the code

Sub Consolidate()


Dim CheckRow, ListRow As Integer
Dim rMax, rMax2 As Integer
Dim OldPart As String
Dim NewPart As String
Dim Selection1 As String
Dim NUM As Integer
'If you tab out your functions like this then it is easier to see how the functions are organised


NUM = 0
rMax = FindLastRow1(3) 'finds the number of rows in master parts list
rMax2 = FindLastRow5(3) 'finds the number of rows in the check list

For CheckRow = 2 To rMax 'Cycle through the check

'Application.ScreenUpdating = False

If Sheets("Master List").Cells(CheckRow, 9) = "" Then 'If the check list line is blank

For ListRow = 2 To rMax2 'Cycle through the list

If Sheets("Merge 2 Master").Cells(ListRow, 1) = Sheets("Master List").Cells(CheckRow, 1) _
And Sheets("Merge 2 Master").Cells(ListRow, 2) = Sheets("Master List").Cells(CheckRow, 2) _
And Sheets("Merge 2 Master").Cells(ListRow, 3) = Sheets("Master List").Cells(CheckRow, 3) Then 'One big AND function, can be broken down if needed, this compares the first 3 columns between sheets

'MsgBox "Match found: Row " & CheckRow & " matches row " & ListRow


NewPart = ListRow & ":" & ListRow 'Makes a Cell location for updated row
OldPart = CheckRow & ":" & CheckRow 'makes a Cell location for row to be updated

Sheets("Merge 2 Master").Select 'activates the check list
Range(NewPart).Select 'selects the updated row
Selection.Copy 'copies
Sheets("Master List").Select 'selects master list
Range(OldPart).Select 'selects the row to be updated
ActiveSheet.Paste 'pastes the update over the old row
NUM = NUM + 1

End If

Next ListRow 'move onto next checklist row and repeat

End If

Next CheckRow ''after checking all checklist rows against single row on master list, cycle to next master list row, rinse and repeat

Selection1 = "2" & ":" & rMax2 'set the full range of data on the Merge sheet
Sheets("Merge 2 Master").Select 'select the sheet
Range(Selection1).Select 'select the range
Selection.Delete 'delete the range

Application.ScreenUpdating = True

MsgBox "Done: " & (NUM) & " Parts Updated"
Sheets("Master List").Select

this basically compares rows between 2 sheets, a master list and an update list. if the row in the master list hasnt been signed off then it is replaced by the matchin row in the update list. if it has been signed off (i.e. the cell <> "") then it is left unchanged.

problem is, on a 1000+ rows this takes an age. so first up i need to make this a bit faster.

really what i need is to compare the two sheets and only replace rows that are different, add additional rows, or remove rows that now no longer appear in the updated list, rather than checking and replacing everything just to be safe. i have no clue on this.

Alex

attached is the workbook i am working on

mdmackillop
10-29-2009, 03:23 AM
First; copy and paste without selecting


Sheets("Merge 2 Master").Range(NewPart).Copy Sheets("Master List").Range(OldPart)

AlexMason
10-29-2009, 03:47 AM
I assume i have to keep the

ActiveSheet.Paste command under that?

AlexMason
10-29-2009, 03:56 AM
that errors out with

Run-time error 1004

Application defined or object defined error