searching for hidden cells?

  • Thread starter Thread starter nxy5jgb02
  • Start date Start date
N

nxy5jgb02

Is there a quick and easy way to find (or otherwise highlight) all the
hidden rows and columns within a workbook? I inherited a workbook with
70+ worksheets and I just realized that the previous user hid some of
the cells with obsolete data, instead of deleting them. I'd like to
eliminate all the hidden data, but I'm hoping that there's a quicker
way than paging through each worksheet and manually looking for missing
rows or columns.

Thanks,
Matt
 
I think that it would be easier to do this:
Edit--Go to--Special, choose Visible cells only, hit OK.
Now format them all with yellow fill.
Now, Ctrl+A or hit the button to the left of A and above the 1, and hit
Format--Column--Unhide, and Format--Row--Unhide.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com
 
You could do that, but it's kind of dangerous.

Lots of developers may hide the old stuff--but lots more hide stuff that they
need, but don't want to clutter the worksheet for the user.

Those hidden rows/columns could contain important formulas, important values, or
even important information that some macro may need.

And on top of that, if there are macros, you may be breaking them.

Option Explicit
Sub testme()

Dim iCtr As Long
Dim wks As Worksheet
Dim LastRow As Long
Dim LastCol As Long

Set wks = ActiveSheet
With wks
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
LastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column

For iCtr = LastRow To 1 Step -1
If .Rows(iCtr).Hidden = True Then
.Rows(iCtr).Delete
End If
Next iCtr

For iCtr = LastCol To 1 Step -1
If .Columns(iCtr).Hidden = True Then
.Columns(iCtr).Delete
End If
Next iCtr
End With

End Sub

And remember that you may break other formulas--in other worksheets or even in
other workbooks.

I'd be very careful and keep a backup (just in case).
 
Dave Peterson wrote...
....
Lots of developers may hide the old stuff--but lots more hide stuff that they
need, but don't want to clutter the worksheet for the user.
Click to expand...

While this may make life slightly easier for developers, it makes life
much, much harder for future maintainers, as the OP's case
demonstrates. If I could give one piece of advice to people developing
large spreadsheet models it'd be NEVER use hidden rows or columns, use
hidden worksheets (and don't hide anything on the hidden worksheets).
Those hidden rows/columns could contain important formulas, important values, or
even important information that some macro may need.
Click to expand...

So true. With regard to macros, they should never use cells to store
information needed only by the macro and not by worksheet formulas. Use
defined names, which are stored with the workbook and can be evaluated
as needed.
And on top of that, if there are macros, you may be breaking them.
Click to expand...

Only if they're hardcoded. As a rule of thumb, macros should NEVER have
ANY cell addresses in them. Macros should ONLY use defined names
referring to ranges. That's basic abstraction. Dismiss it at your
peril.

....
And remember that you may break other formulas--in other worksheets or even in
other workbooks.
Click to expand...

Always best to trace dependents before deleting anything. Also doesn't
hurt to use a new worksheet to hold a list of all worksheet names in
the workbook (lots of macros and udfs to generate such lists in the
Google Groups archives for the Excel newsgroups), and in the columns to
the right of the name formulas like

=COUNTIF(INDIRECT("'"&A1&"'!A1:IV65536"),"#NULL!")

=COUNTIF(INDIRECT("'"&A1&"'!A1:IV65536"),"#DIV/0!")

=COUNTIF(INDIRECT("'"&A1&"'!A1:IV65536"),"#VALUE!")

=COUNTIF(INDIRECT("'"&A1&"'!A1:IV65536"),"#REF!")

=COUNTIF(INDIRECT("'"&A1&"'!A1:IV65536"),"#NAME?")

=COUNTIF(INDIRECT("'"&A1&"'!A1:IV65536"),"#NUM!")

=COUNTIF(INDIRECT("'"&A1&"'!A1:IV65536"),"#N/A")

=COUNT(INDIRECT("'"&A1&"'!A1:IV65536"))

=COUNTIF(INDIRECT("'"&A1&"'!A1:IV65536"),"*")

which return the number of cells evaluating to each of the error
values, to numbers and to text, repectively, in the worksheet named in
A1. Note: recalc could be SLOW. If these change in workbooks other than
the one in which you delete anything, that's a clear indicator there's
a dependency. Always be prepared to undo, so macros ain't a good idea.
 
Back
Top