VBA code optimization

[Lorlai]

I am comparing two sheets, trying to find changes. These sheets contain large amounts of data (From Columns A:DA, and over 9,000 rows). Because of this, my code is incredibly slow, taking up to 30 min to run. Is there a way to optimize my code? I have already turned off automatic calculations and the screen update. Any help with this would be appreciated!!

Sub Test()
Application.ScreenUpdating = False
'Getting Ready to Name the final sheet
Dim TodayDate As String
TodayDate = Format(Date, "mmm-dd-yyyy")

'Choosing a previous file
MsgBox "Please choose a previous File"
PreviousWorkbook = Application.GetOpenFilename _
(Title:="Please choose a previous file", _
FileFilter:="Excel Files *.xls* (*.xls*),")
''
If PreviousWorkbook = False Then
MsgBox "No file specified.", vbExclamation, "Error"
Exit Sub
Else
Workbooks.Open Filename:=PreviousWorkbook
End If

'Copying the Source data from the Previous File to the Changes Workbook
Sheets("RM Data List").Select
ActiveSheet.Copy After:=Workbooks("Changes.xlsm").Sheets(1)
ActiveSheet.Name = "Previous"

'Choosing a current file
MsgBox "Please choose the Current File"
CurrentWorkbook = Application.GetOpenFilename _
(Title:="Please choose the current file", _
FileFilter:="Excel Files *.xls* (*.xls*),")
''
If CurrentWorkbook = False Then
MsgBox "No file specified.", vbExclamation, "Error"
Exit Sub
Else
Workbooks.Open Filename:=CurrentWorkbook
End If

'Copying the Source Data from the Current file to the Changes Workbook
Sheets("Source Data").Select
ActiveSheet.Copy After:=Workbooks("Changes.xlsm").Sheets(1)
ActiveSheet.Name = "Current"


Application.Calculation = xlCalculationManual

Sheets("Previous").Select
Columns("A:DZ").Select
    Selection.NumberFormat = "General"
    Selection.Replace What:="", Replacement:="BLANK", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Range("A1").Select

Sheets("Current").Select
Columns("A:DZ").Select
    Selection.NumberFormat = "General"
    Selection.Replace What:="", Replacement:="BLANK", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Range("A1").Select

Do
Sheets("Previous").Select
CellValue = ActiveCell.Value

Sheets("Current").Select
    If ActiveCell.Value <> CellValue Then
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    End If
ActiveCell.Offset(0, 1).Select

Sheets("Previous").Select
ActiveCell.Offset(0, 1).Select

If IsEmpty(ActiveCell) Then
    Sheets("Previous").Select
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Cells(ActiveCell.Row, 1).Select
    Sheets("Current").Select
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Cells(ActiveCell.Row, 1).Select
End If

Loop Until IsEmpty(ActiveCell)

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Lorlai]

Why not use the ID to find the 2 related records and compare them?

I'm not quite certain how this could be done. I thought that was what I was already doing in a less efficient way by going cell by cell. How could two whole rows (records?) be compared in excel?

 

[Norie]

Did I miss something?

Did the previous code compare only records with the same ID with each other?

 

[Lorlai]

No no, you are correct, I was comparing everything on the spreadsheet cell by cell, irregardless of matching ID.

I guess I am not quite understanding

Why not use the ID to find the 2 related records and compare them?

That's exactly how it would be done in something like Access and there's no reason you can't do something similar in Excel.

Can you explain this a little further?

And thank you for all of your help so far, I really appreciate it! You are saving me from bashing my head against a wall

 

[Norie]

Just take an ID from the Current sheet, search for it in the Previous sheet.

Then if it's found compare the 2 records cell by cell, if it's not found then the record on the Current sheet is new.

You could do that for all the records on the Current sheet, and you would only need to compare if there's a match.

Any closer to making any sense whatsoever?

 

[Lorlai]

I think I am understanding more of what you are saying. Just to 100% clarify, this row is a record:

Name Product Manager ID Color Qty Price etc etc
Cool Toy Bob Smith 12345 Blue 8 $45.69 .....

Where the ID is the "Primary Key".

In general, there won't be many records being added or deleted. The biggest change that will occur will be within the record itself.

It seems to me, from what I understand, that the method of only comparing matching records is great, except my data will still be too large for my method of then comparing cell by cell.

I'm thinking that with this method, you will compare the two worksheets by ID, then if 12345 matches on both sheets, then compare the whole row? Cell by cell through the row as before?

 

[Norie]

Unless each record has relevant data in each column of the row you wouldn't need to compare each cell in the row.

In this case as far as I can see the data for each record is only in 9-10 cells in the row, all the other cells will be blank.

 

[Lorlai]

Sadly, all of the columns contain relevant information that could have changed. The example I gave was purely dummy data; the real record has data in columns "A" to "EA"; over 70 columns.

Sometimes data will be blank in one column (if a product is to be released in the winter, quantities do not need to be available in the spring and summer and will be left blank).

Am I heading right back to square one with this?

 

[Norie]

I actually meant every column in the row, not the no of columns with data.

Mind you 70 columns/fields is quite a lot but not impossible to work with.

Are all 70 definitely relevant, eg they all could have been changed?

 

[Lorlai]

There are a few columns that 100% will not change, maybe 10 or so randomly distributed throughout the rest.

A new thought; maybe importing the spreadsheets into two tables in access and running a union query to find all changes, then exporting the changes into a new spreadsheet and running a lookup to compare them to the current worksheet?

 

[Norie]

You could probably do this in Access without a UNION query, which probably isn't what should be used anyway.

In Access you can create, via the main query wizard, an unmatched query.

This will basically return the records that don't match.

It's normally used with only one or two fields but could be adapted for as many fields as you wanted.

Or you could create separate queries to check different fields.

There's also something you could do in Excel using conditional formatting but it would rely on the two sets of data having the records with the same IDs in the same rows.

eg if the record with ID 1234 is in row 10 of the Current sheet it's also in row 10 of the Previous sheet

All you would need to do would be to copy the sheets side by side, with a column inbetween and use simple formulas.

It can also be done without conditional formatting.