Excel / VBA / Adding progress bar

Question

The code below searches for duplicates in different sheets of my work book. The issue is that it takes a little while for it to be done. How can I add a progress indicator in the status bar at the bottom?

Thank you & Kind regards.

Sub dup()
    Dim cell As Range
    Dim cella As Range
    Dim rng As Range
    Dim srng As Range
    Dim rng2 As Range
    Dim SheetName As Variant

    Application.ScreenUpdating = False
    Worksheets("Screener").Range("A7:A15").Interior.ColorIndex = xlNone

    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Set srng = Sheets("Screener").Range("A7:A2000")
    Set rng = Sheets("Rejected").Range("A7:A2000")
    Set rng2 = Sheets("Full Data").Range("A7:A2000")

    For Each cell In rng
        For Each cella In srng
            If cella = cell Then
                cella.Interior.ColorIndex = 4
                cella.Offset(, 1) = "Rejected"
            End If
       Next cella
    Next cell

    For Each cell In rng2
        For Each cella In srng
            If cella = cell Then
                cella.Interior.ColorIndex = 5.5
                cella.Offset(, 1) = "Reported"
            End If
        Next cella
    Next cell

    Application.ScreenUpdating = True

End Sub

B Slater · Accepted Answer · 2017-08-03 14:04:34Z

One thing you can do is speed up your code, there's a few things I'd change about it in its current state,

It's really slow to access range objects and their value, you should instead load the ranges into a variant array and cycle through the arrays
If you find a duplicate, you still go through and check every other range in both arrays which wastes time, you should skip to the next range once you've found a duplicate

With that in mind I've rewritten your code like this, it's completely equivalent and runs in less than a second on my machine:

Sub dup()
    Dim i As Integer, j As Integer
    Dim RejectVals As Variant
    Dim ScreenVals As Variant
    Dim FullDataVals As Variant
    Dim SheetName As Variant
    Dim output() As String

    'Push column on 'Screener' sheet to the right to make space for new output
    Worksheets("Screener").Range("A7:A15").Interior.ColorIndex = xlNone
    Worksheets("Screener").Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    'Pull the values from your 3 ranges into arrays to avoid costly cycling through ranges
    ScreenVals = Application.Transpose(Sheets("Screener").Range("A7:A2000").Value)
    RejectVals = Application.Transpose(Sheets("Rejected").Range("A7:A2000").Value)
    FullDataVals = Application.Transpose(Sheets("Full Data").Range("A7:A2000").Value)

    'Resize output column to be same size as column we're screening because
    'we're going to place it in the column adjacent
    ReDim output(LBound(ScreenVals) To UBound(ScreenVals))

    'Cycle through each value in the array we're screening
    For i = LBound(ScreenVals) To UBound(ScreenVals)
        'Skip without checking if the cell is blank
        If ScreenVals(i) = vbNullString Then GoTo rejected

        'Cycle through each value in the 'FullData' array
        For j = LBound(FullDataVals) To UBound(FullDataVals)
            'If it's a duplicate then
            If ScreenVals(i) = FullDataVals(j) Then
                'Set the relevant value in the output array to 'Reported'
                output(i) = "Reported"

                'Colour the cell on the 'screener' page
                Worksheets("Screener").Cells(i + 6, 1).Interior.ColorIndex = 5.5

                'Skip checking more values
                GoTo rejected
            End If
        Next j

        'Next cycle through all the 'Rejected' values
        For j = LBound(RejectVals) To UBound(RejectVals)
            'If it's a duplicate then
            If ScreenVals(i) = RejectVals(j) Then
                'Set the relevant value in the output array to 'Rejected'
                output(i) = "Rejected"

                'Colour the cell
                Worksheets("Screener").Cells(i + 6, 1).Interior.ColorIndex = 4

                'Skip checking any more values
                GoTo rejected
            End If
        Next j
rejected:
    Next i

    'Pop the output array in the column next to the screened range
    Worksheets("Screener").Range("B7:B2000") = Application.Transpose(output)
End Sub

I check for duplicates in the 'Full Data' sheet first which means if there is a duplicate in both tables then it will default to 'Reported' and a yellow cell, if you'd like the opposite you can swap the order of the loops.

Let me know if there's anything you don't understand

Thank you for the quick reply, its working great. is there a way to omit the blank cells from the ranges ? Cheers !
Hi Ocean! I've changed my code so it'll skip over any cells in the 'Screener' worksheet that are blank, is that what you meant?
If this works for you, can you please tick it as the accepted answer, cheers!

Collectives™ on Stack Overflow

Excel / VBA / Adding progress bar

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related