Help Streamling Hide Empty Cells Macro

[chuckdanger]

Hi, attempted to create a code to hide all "0" cell rows in a given column. These cells consist of a lookup seeking mail dates that have not occurred yet in data, so they are returning a 1/0/1900 value for the time being. Came up with the following code. I am not liking the run time on it and would appreciate input on how to make it more efficient. The column range "lr" never exceeds 1000, if that's a reference point.

Sub HideEmpties()

Dim lr As String


StartTime = Timer


Application.Calculation = xlManual = True


lr = Range("J" & Rows.Count).End(xlUp).Row


        Dim cell As Range
             For Each cell In Range("J1:J" + lr)
            cell.EntireRow.Hidden = cell.Value = 0
        Next cell
        
Application.Calculation = xlAutomatic = True


MinElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "This code ran successfully in " & MinElapsed & " minutes", vbInformation


End Sub

 

[tonyyy]

I've heard that using Find is more efficient than For Each. You might try something like...

Sub FindMultipleOccurrences()

Dim rngSearch As Range, rngLast As Range, rngFound As Range
Dim strFirstAddress As String
Dim LastRow As Long

LastRow = Cells(Rows.Count, "J").End(xlUp).Row
Set rngSearch = ActiveSheet.Range("J1:J" & LastRow)
Set rngLast = rngSearch.Cells(rngSearch.Cells.Count)
Set rngFound = rngSearch.Find(What:=0, After:=rngLast, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not rngFound Is Nothing Then
    strFirstAddress = rngFound.Address
    Do
        Set rngFound = rngSearch.FindNext(rngFound)
        rngFound.EntireRow.Hidden = True
    Loop Until rngFound.Address = strFirstAddress
End If

End Sub

I'd be interested in seeing your timing results.

Oh, this looks for "0" - not 1/0/1900.

Cheers,

tonyyy

 

[chuckdanger]

Thanks, I will try and implement this, and keep you posted if I am seeing it go any quicker. Right now, I have it running 20-22 sec each tab for 21 tabs.

 

[tonyyy]

In either case, you might shorten the run time by...

Inserting "Application.ScreenUpdating = False" just below the Sub statement, and

inserting "Application.ScreenUpdating = True" just above the End Sub statement.

 

[chuckdanger]

Thank You !! you have changed my life with those two lines. I ve seen it in use before, but never realized how good of a tool that is. The timer doesn't even have a chance to run 0:00:00. THANKS SO MUCH

 

[tonyyy]

You're welcome. Glad I could help.