Excel

Hide Empty Rows, Unhide Others

Ease of Use

Intermediate

Version tested with

2000, 2003 

Submitted by:

johnske

Description:

All specified unused rows are hidden when the sheet is activated and all specified used rows are unhidden 

Discussion:

You may have a summary sheet where, for ease of reading, you only want rows with non-zero entries to be shown. 

Code:

instructions for use

			

'<< Code for worksheet module >> Option Explicit Private Sub Worksheet_Activate() Dim HiddenRow&, RowRange As Range, RowRangeValue& '***************************** '< Set the 1st & last rows to be hidden > Const FirstRow As Long = 4 Const LastRow As Long = 20 '< Set the columns that may contain data > Const FirstCol As String = "B" Const LastCol As String = "G" '***************************** ActiveWindow.DisplayZeros = False Application.ScreenUpdating = False For HiddenRow = FirstRow To LastRow '(we're using columns B to G 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 = True End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Tools/Project Explorer
  4. Select the worksheet module for the linked sheet
  5. Copy and paste the code into this Module
  6. Now select File/Close and Return To Microsoft Excel
  7. Save your changes...
 

Test the code:

  1. Download the attachment to test the code
  2. Sheet2 contains links to Sheet1, note that some rows are hidden
  3. (You can select all and Right-click and then choose Unhide to view all)
  4. Go to Sheet1 and enter a value in any unused row
  5. Now look at Sheet2 and note this same (numbered) row is now visible
 

Sample File:

HideBlankLinkedRows.zip 8.94KB 

Approved by mdmackillop


This entry has been viewed 231 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express