Automatcally Hiding Rows in Excel 2000

  • Thread starter Thread starter Tom Starr
  • Start date Start date
T

Tom Starr

Is there a way of automatically hiding rows in Excel 2000 based on a rule
(ie if there is no data in a row, we want to hide that row without doing it
manually)? We have a couple of ideas which involve VB code which reacts to
data within that row but if anyone has done this before or knows of a way to
do it, that would be great.

Thanks in advance!

Tom Starr
 
this isn't quite automatic, but I like it better than automatic (how would you
unhide it when you need it and keep it unhidden?)

Apply Data|Filter|Autofilter to the range.

Filter on non-blanks to show only those cells that are, er, non-blank.

(I guess that this'll work ok if there's always a column that gets filled in
first.)

But this macro (not quite automatic--you have to run it) will do it for you:

Option Explicit
Sub testme01()
Dim myRow As Long
With Worksheets("sheet1")
For myRow = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If Application.CountA(.Rows(myRow)) = 0 Then
.Rows(myRow).Hidden = True
End If
Next myRow
End With
End Sub
 
Back
Top