Hide Excel Rows faster I've built a VBA Excel macro that works on a spreadsheet of duplicated files, and displays only groups in which at least one duplication is owned by the current user.

The spreadsheet lists file metadata, one file per row, grouped with other files whose content is identical, i.e. "duplicated". Each such duplication group is preceded by a title row in bold.

The macro uses text boldness to determine duplication groups, and checks within each group whether any file's owner (represented in column 7) is the currently logged user.
All groups with no file owned by the user get hidden.
(auto-filtering would not solve this problem because I want the user to see the other files in the duplication group, i.e. see files from other owners with the same content as theirs) 

It works exactly as intended, but too slowly. It takes one or two minutes to run through a spreadsheet about 80 thousand lines long. Anyone can see a way to speed it up?

I had the idea of maybe hiding every row first (which I assume can be done much faster) and then de-hide what should be shown. But I couldn't find a command to mass-hide.

Also, since we're here, what best practices am I missing? I'm a complete novice to VBA, though I'm an experienced programmer in other languages.

Grateful for any help.

'Operates on .xslx report of network file duplication, as output by TreeSize tool.
'Hides all Duplication Groups not involving the currently logged user as a file Owner
'(regular filtering does not work because it hides the other duplicates which have a different owner)
'TODO: speed it up. It currently takes ~1min to run. Probably hide-all then unhide-selected will be faster.
Private Sub My_duplicates_Click()
    Dim hidIt As Boolean
    Dim foundInGroup As Boolean

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    login = Environ("Username")
    ownerCol = 7
    rowNum = 3 'skip header
    Do While Not IsEmpty(Cells(rowNum, 1))
        If Cells(rowNum, ownerCol).Font.Bold = True Then 'bold text indicates the beginning of a duplication group
            foundInGroup = False
            groupMemberRow = rowNum + 1
            Do While Not IsEmpty(Cells(groupMemberRow, 1)) And Not Cells(groupMemberRow, ownerCol).Font.Bold = True
                If Cells(groupMemberRow, ownerCol).Value2 = login Then
                    foundInGroup = True
                End If
                groupMemberRow = groupMemberRow + 1
            Loop
            If foundInGroup = False Then
                hidIt = True
            Else
                hidIt = False
            End If
            Cells(rowNum, ownerCol).EntireRow.Hidden = hidIt
            groupMemberRow = rowNum + 1
            Do While Not IsEmpty(Cells(groupMemberRow, 1)) And Not Cells(groupMemberRow, ownerCol).Font.Bold = True
                Cells(groupMemberRow, ownerCol).EntireRow.Hidden = hidIt
                groupMemberRow = groupMemberRow + 1
            Loop
            rowNum = groupMemberRow - 1 'counting on next increment to place it back on the next group header
        End If
        rowNum = rowNum + 1
    Loop
End Sub
Auto Filtering


  (auto-filtering would not solve this problem because I want the user to see the other files in the duplication group, i.e. see files from other owners with the same content as theirs)


Auto Filtering could definitely solve this problem.  The way to go about it would be to add a helper column, set the values in the column to "Show" if you want to show the column and then filter the helper column by the word "Show".  You could even hide this column.

Hiding Rows


  I had the idea of maybe hiding every row first (which I assume can be done much faster) and then de-hide what should be shown. But I couldn't find a command to mass-hide.


Yes, this undoubtedly faster.  Consider that you have 80K rows and you only need to see 1K rows.  Instead of performing 80K + 1 show/hide operations, hiding all the rows and then unhiding the rows you want will reduce the number of operations to  1K + 1.  Union() is much faster than Show/Hide. It would be even faster if you were to Union() all the ranges that need to be shown together and only have 1 hide operation amd 1 show operation.  

Note: Concatenating the Range addresses is considerably faster than Union().  This can get somewhat complicated because there is a limit to the address length.  To maximize performance, use a combination of address concatenation and Union().   

Option Explicit

MSDN - Option Explicit Statement


  Use Option Explicit to avoid incorrectly typing the name of an existing variable or to avoid confusion in code where the scope of the variable is not clear.


One of the best of the "Best Practices" is to always declare and Type your variables.  The more information that you give the compiler the faster the compiler can catch errors.

Declaring Constants

MSDN - Declaring Constants


  By declaring a constant, you can assign a meaningful name to a value. You use the Const statement to declare a constant and set its value. After a constant is declared, it cannot be modified or assigned a new value.


A variable should be used when its value is either not know prior to compiling or will vary throughout the course of code execution.  A value that will remain always remain constant should be declared as a such.


 Const ownerCol As Long = 7



A Faster Way

Sub ShowUserDuplicates()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Const OWNER_COLUMN As Long = 7
    Dim cell As Range, VisibleRows As Range
    Dim flagUser As Boolean
    Dim login As String

    login = Environ("Username")
    With Worksheets("Sheet1")
        'Hide All the Rows
        .UsedRange.Offset(2).EntireRow.Hide
        'Iterate though the cells in the Owner Column
        For Each cell In Range("A3", .Range("A" &amp; .Rows.Count).End(xlUp)).Offset(0, OWNER_COLUMN - 1)
            If cell.Font.Bold Then flagUser = cell.Value = login

            If flagUser Then
                If VisibleRows Is Nothing Then
                    Set VisibleRows = cell
                Else
                    Set VisibleRows = Union(VisibleRows, cell)
                End If
            End If
        Next
        If Not VisibleRows Is Nothing Then VisibleRows.EntireRow.Hidden = False
    End With

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False
    Application.EnableEvents = False
End Sub