CODEWITHSUNDEEP
excelvba
user1556274
user1556274

Reputation: 37

How to speed up VBA code for hiding rows?

With VBA I have created a toggle button that hides and unhides rows, however it's really slow. To hide 200 rows it takes roughly 2 minutes. Is there a way I can speed this code up? My goal would be under 10 seconds if that's at all possible.

Here's my code:

Option Explicit

Private Sub ToggleButton1_Click()

Dim bHide As Boolean
Dim rCell As Range
bHide = ToggleButton1.Value

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each rCell In Range("AC10:AC800")
If rCell.Value = 1 Then
If Not rCell Is Nothing Then
Rows(rCell.Row).Hidden = bHide
End If
End If
Next 

Application.ScreenUpdating = True
Application.EnableEvents = True 

End Sub

Thanks for any help!

Upvotes: 0

Views: 1426

Answers (3)

FaneDuru
FaneDuru

Reputation: 42256

I think that using AutoFilter is the fastest method, needing a shorter piece of code.

Private Sub rngFilterHide()
  Dim sh As Worksheet, sR As Range, rngHhide As Range

    Set sh = ActiveSheet 'Use here your sheet name
    Set sR = sh.Range("AC10:AC800")
    sR.AutoFilter field:=1, Criteria1:="=1", VisibleDropDown:=False
      Set rngHhide = sR.SpecialCells(xlCellTypeVisible)
      sh.AutoFilterMode = False
      rngHhide.EntireRow.Hidden = ToggleButto1.Value
      If sh.Range("AC10") <> 1 Then sh.Rows("10:10").Hidden = Not ToggleButto1.Value
End Sub

Upvotes: 0

JohnyL
JohnyL

Reputation: 7162

Another way is just to gather address. In the accepted answer you constantly need to check If rangeToHide Is Nothing. In this method, however, you don't have to do it.

Sub HideUnhide()
    Dim addr, cell
    For Each cell In [AC10:AC800]
        If cell = 1 Then addr = addr & cell.Address(0, 0) & ","
    Next
    If Len(addr) > 0 Then
        addr = Left$(addr, Len(addr) - 1) '//Get rid of last comma
        Range(addr).EntireRow.Hidden = ToggleButto1.Value
    End If
End Sub

Upvotes: 0

Josh Eller
Josh Eller

Reputation: 2065

In VBA, reading and writing to worksheets is the biggest time killer. So, you want to minimize how often you interact with worksheets.

Luckily, you can read multiple values or hide multiple rows in a single command, instead of having a command for every single value or row.

Something like this:

Private Sub ToggleButton1_Click()
    Dim ws As Worksheet
    Dim rangeToCheck As Range
    Dim rangeToHide As Range
    Dim sheetData() As Variant
    Dim curRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1") 'Change to your worksheet name
    Set rangeToCheck = ws.Range("AC10:AC800")

    sheetData = rangeToCheck.Value  'Read all data from the range into an array at once

    For curRow = LBound(sheetData) To UBound(sheetData)
        If sheetData(curRow, 1) = 1 Then 'Check contents of each row, and add to range to hide if the check is true
            If rangeToHide Is Nothing Then
                Set rangeToHide = rangeToCheck.Rows(curRow).EntireRow
            Else
                Set rangeToHide = Union(rangeToHide, rangeToCheck.Rows(curRow).EntireRow)
            End If
        End If
    Next curRow

    'If at least one row was found, hide all rows at once
    If Not rangeToHide Is Nothing Then rangeToHide.EntireRow.Hidden = ToggleButton1.Value
End Sub

Upvotes: 2

Related Questions