PDA

View Full Version : Sleeper: Automatically set merged cell height



Phprahl
07-06-2005, 04:26 AM
Hello

I have a problem with a macro that I have found and tries to use in my workbook.

It is a macro that will adjust the row height of merged cells. It works fine until I e.g. finds out that I wrote something wrong in the cell and want to delete it by pressing the delete key. Then I get into a runtime error. Anyone that can help me with that? Another thing I would like to have some help with is to "reset" the row hight if I decide to remove text that has been written in the merged cell. Is it possible to do that?

This is what i have in my worksheet:


Private Sub Worksheet_Change(ByVal Target As Range)
Call AutoFitMergedCellRowHeight(Target)
End Sub

and this is the macro code in a module:


Sub AutoFitMergedCellRowHeight(Target As Range)
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range, RangeWidth As Single
Dim TargetWidth As Single, PossNewRowHeight As Single
If Target.MergeCells Then
With Target.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
TargetWidth = Target.ColumnWidth
RangeWidth = .Width
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
While .Cells(1).Width < RangeWidth
.Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 0.5
Wend
.Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 0.5
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = TargetWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub


Rgds

shades
07-06-2005, 05:53 AM
Howdy. This is a cross-post (http://www.ozgrid.com/forum/showthread.php?p=180583#post180583).

Normally a person should not post to more than one board the same question at the same time. This can be frustrating for those who desire to help, but discover that someone else had been working on it and provided a solution elsewhere.

Phprahl
07-06-2005, 05:56 AM
Sorry...

I really need help so I thought if I posted it on another forum as well I might get some help...

/Php

shades
07-06-2005, 05:57 AM
The need for help is real. Just an appropriate way to get it. :)

Been there, done that, can't remember a thing!