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