I've got a spreadsheet using VBA to autohide blank rows. Code is working on one worksheet, and 'error 13' on two others: anyone interested in helping to resolve??
Not a competent VBA user and sourced the code from the web. So trying to debug has proved incredibly difficult as I am unsure of what the code means! (It also doesn't work too fast)
Also note that the rows are not 'blank' as they are conditionally pulling data from a root worksheet - naturally, some rows do not meet the criteria and look empty though are technically " " (if this is helpful)... I do like the way the current version runs when the worksheet is activated!
Option Explicit
Private Sub Worksheet_Activate()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 3
Const LastRow As Long = 500
'< Set the columns that may contain data >
Const FirstCol As String = "A"
Const LastCol As String = "H"
'*****************************
ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False
For HiddenRow = FirstRow To LastRow
'(we're using columns A to H here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)
If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If
Next HiddenRow
Application.ScreenUpdating = False
End Sub
Bookmarks