CODEWITHSUNDEEP
excelvba
irozak
irozak

Reputation: 128

Show only those rows where a cell value is bold

Trying to show just the subtotal rows (which are bolded) and hide everything else. Code crashes when it hits If c.Value.Font.Bold = False Then. Thanks

Sub HideEmptyRows() 'quote stack
    
Dim col As String
col = Range("c2").Value    'column to check
Dim sta As Integer
sta = Range("C3").Value    'start row
Dim fin As Integer
fin = Range("C4").Value    'finish row
    
Application.ScreenUpdating = True
    
Dim c As Range
For Each c In Range(col & sta & ":" & col & fin)
    
If c.Value.Font.Bold = False Then
c.EntireRow.Hidden = True
    
Else
c.EntireRow.Hidden = False
End If

Next c

End Sub

Upvotes: 1

Views: 187

Answers (1)

VBasic2008
VBasic2008

Reputation: 54777

Show Only 'Bold' Rows

  • You can improve performance by turning screen updating off while the rows are being manipulated.
  • You can improve performance by hiding the (entire) rows of the complete range and then unhiding the 'bold' rows. You could do it the other way around: show the rows of the complete range and then hide those that are not 'bold' (see the three 'boolean-commented' lines (False,False,True)).
Option Explicit

Sub HideEmptyRows() 'quote stack
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' be more specific

    Dim col As String: col = ws.Range("C2").Value   'column to check
    Dim sta As Long: sta = ws.Range("C3").Value   'start row
    Dim fin As Long: fin = ws.Range("C4").Value   'finish row
        
    Dim rg As Range: Set rg = ws.Range(col & sta & ":" & col & fin)
    
    Application.ScreenUpdating = False
        
    ' Hide the rows of the complete range.
    rg.EntireRow.Hidden = True ' False
    
    Dim c As Range
    
    ' Show bold rows only.
    For Each c In rg.Cells
        If c.Font.Bold = True Then ' False
            c.EntireRow.Hidden = False ' True
        End If
    Next c

    Application.ScreenUpdating = True
    
    'MsgBox "Showing only subtotals.", vbInformation

End Sub

Upvotes: 0

Related Questions