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
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