Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to auto hide an empty row with VBA?
Is it possible to have Excel hide a row automatically if there is no
entry there? My worksheet Subtotals data on 5 rows, then creates a Total on the 6th row. If there is no data on any row, can I hide them so the Total line appears right below the last entry? For example: Subtotal 1 (SUMIF function used) Subtotal 2 (SUMIF function used) Subtotal 3 (SUMIF function used) Empty Subtotal 4 (SUMIF function used) Empty Subtotal 5 (SUMIF function used) TOTAL (SUM of all above) My first guess is to use Change event, looking at a cell that would count the number of subtotals that are not empty, then use VBA to hide the empty ones...? --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to auto hide an empty row with VBA?
Hi,
Here's a macro that hides all rows that have non-numeric data, or zeros, in Column B, and unhides them again if their status changes. Modifying it to work a different range of columns and on a Change event basis (eg Worksheet_Calculate or WorksheetChange) would be fairly trivial. Sub CleanUp() Dim CurrentRow As Long Dim UsedRows As Range On Error GoTo Abort Set UsedRows = ActiveSheet.UsedRange.Rows For CurrentRow = UsedRows.Rows.Count To 1 Step -1 If Application.WorksheetFunction.Sum(UsedRows.Rows(Cu rrentRow).Columns("B:B")) = 0 Then UsedRows.Rows(CurrentRow).EntireRow.Hidden = True Else UsedRows.Rows(CurrentRow).EntireRow.Hidden = False End If Next CurrentRow ' If only hiding for printing purposes, use the next two lines to print or preview then restore the worksheet 'ActiveWindow.SelectedSheets.PrintPreview 'ActiveSheet.Rows.EntireRow.Hidden = False Abort: End Sub Change the 'Sum" parameter to "Count" if only non-numeric values are to be used (i.e. zeros are left alone, but empty and text-only rows in the defined columns are not). Cheers --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.561 / Virus Database: 353 - Release Date: 13/01/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically hide a row if it is empty | Excel Discussion (Misc queries) | |||
How can i auto hide empty rows in printing in excel? | Excel Discussion (Misc queries) | |||
Is there a way to automatically hide empty rows? | Excel Worksheet Functions | |||
Hide row with empty cells | Excel Programming | |||
Hide row with empty cells | Excel Programming |