Hidden columns affect row height

[David Lewis]

The hidden columns in a spreadsheet are affecting the row height of a
row with a lot of word-wrapped cells. In other words, if I hide a
column with a "tall" cell (many lines of text) I'd expect the row of
that tall cell to decrease in height. Instead, the height of the
hidden cell continues to influence the height of the whole row, giving
it much more height than it needs for the cells that are showing.

Example

A B C
row 1: x abc y
def
ghi

When all three columns are showing, row 1 is tall enough for all three
lines of B1. When I hide column B, I expect the height of row 1 to
reduce, since it no longer has to display B1. Bu instead, it retains
its extra height (and looks crummy, of course).

I'm pretty sure this jsut started happening, perhps with the most
recent security update to Win XP Pro. But maybe my memory is failing.
In any case, it's not very good behavior, and I wonder if there is a
way around it.

Thanks. --David.

 

[Dave Peterson]

How about a macro that will go back and adjust the rowheights?

This'll work if you're using xl2k or higher. (I used pastespecial that copied
the column widths and that was added in xl2k.)

Option Explicit
Sub testme()

Dim myRng As Range
Dim myRow As Range
Dim testWks As Worksheet
Dim curWks As Worksheet

Set curWks = ActiveSheet
Set testWks = Worksheets.Add
With curWks
For Each myRow In .UsedRange.Rows
Set myRng = Nothing
On Error Resume Next
Set myRng = myRow.EntireRow.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
myRng.Copy
With testWks.Range("a1")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteColumnWidths
.EntireRow.AutoFit
myRow.RowHeight = .EntireRow.RowHeight
.EntireRow.Delete
End With
End If
Next myRow
End With

Application.DisplayAlerts = False
testWks.Delete
Application.DisplayAlerts = True

End Sub

It cycles through the used range and copies the visible cells of each row to a
new sheet. Adjusts the height there, and uses that for the height in the real
worksheet.

 

[Dave Peterson]

And in the group of .pastespecial lines, add one more:

.PasteSpecial Paste:=xlPasteFormats

 

[David Lewis]

Thanks, but I'd like to avoid writing code, or at least
that much code. As a former developer, I'm not averse to
coding in principle, but it increases the maintenance
burden signficantly.

The following non-coding solution was suggested to me: turn
word-wrap off for all hidden columns. That's a bit of a
nuisance, especialy when you unhide and want word wrap
back, but should do the trick. A bigger solution, which I
will get to at some point, is to put the data in Access and
select only the columns I want for export to Excel.

Of course, this could be considered a bug -- I think of it
as such -- so I hope MS will get around to fixing it
someday. Is it on the bug list?

Thanks. --David.

 

[Dave Peterson]

Turning off word wrap is too easy <vbg>. Maybe you could have a macro toggle it
for you.