Prev: change Pivot table
Next: problems with a localconnection to a cub file
From: Martin on 18 Jan 2007 08:58
Hello there,

I am using the "AutoFitMergedCellRowHeight" macro which is working fine for
merged cells across several columns on one row such as range(A1:C1). But is
does not seem to work when the merged cell is across several columns and also
across several rows such as range(A1:C4). Does anyone have any idea of how to
solve this problem.

Any help much appreciated.

--
Regards,

Martin
From: Greg Wilson on 18 Jan 2007 15:27
Maybe this:

Sub TestAutoFit()
AutoFitMergedCellRowHeight ActiveCell
End Sub

Sub AutoFitMergedCellRowHeight(Rng As Range)
Dim MergedCellRgWidth As Single
Dim CurrCell As Range
Dim C1Width As Single, PossNewRowHeight As Single

If Rng.MergeCells Then
With Rng.MergeArea
If .WrapText = True Then
Application.ScreenUpdating = False
C1Width = .Cells(1).ColumnWidth
For Each CurrCell In Intersect(.Cells(1).EntireRow, .Cells)
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .Cells(1).RowHeight
.Cells(1).ColumnWidth = C1Width
.MergeCells = True
.RowHeight = PossNewRowHeight / .Rows.Count
Application.ScreenUpdating = True
End If
End With
End If
End Sub

Regards,
Greg
From: Greg Wilson on 18 Jan 2007 15:42
I should also advise you that the macro will expand / contract the merged
rows evenly. I presume you also have nonmerged cells sharing the same rows.
Else, you wouldn't need to merge the range across rows but rather just
increase the row height. This being the case, you need to specify how you
want the rows to resize so they don't conflict with the nonmerged cells.

Greg



"Martin" wrote:

> Hello there,
>
> I am using the "AutoFitMergedCellRowHeight" macro which is working fine for
> merged cells across several columns on one row such as range(A1:C1). But is
> does not seem to work when the merged cell is across several columns and also
> across several rows such as range(A1:C4). Does anyone have any idea of how to
> solve this problem.
>
> Any help much appreciated.
>
> --
> Regards,
>
> Martin
From: Martin on 19 Jan 2007 15:35
Thanks a lot Greg,

As you have mentioned the rows resize evenly. Is there a way to specify a
minimum row height within the macro you provided. Or is it easier for me to
run another macro resizing to the minimum row height?

What do you think?
--
Many thanks for your help and kind regards,

Martin


"Greg Wilson" wrote:

> I should also advise you that the macro will expand / contract the merged
> rows evenly. I presume you also have nonmerged cells sharing the same rows.
> Else, you wouldn't need to merge the range across rows but rather just
> increase the row height. This being the case, you need to specify how you
> want the rows to resize so they don't conflict with the nonmerged cells.
>
> Greg
>
>
>
> "Martin" wrote:
>
> > Hello there,
> >
> > I am using the "AutoFitMergedCellRowHeight" macro which is working fine for
> > merged cells across several columns on one row such as range(A1:C1). But is
> > does not seem to work when the merged cell is across several columns and also
> > across several rows such as range(A1:C4). Does anyone have any idea of how to
> > solve this problem.
> >
> > Any help much appreciated.
> >
> > --
> > Regards,
> >
> > Martin
From: Greg Wilson on 19 Jan 2007 23:04
Pass two arguments in your call to the macro:
AutoFitMergedCellRowHeight ActiveCell, 12.75

Add an extra argument to the main macro:
Sub AutoFitMergedCellRowHeight(Rng As Range, Limit As Single)

Substitute this line:
..RowHeight = Application.Max(PossNewRowHeight / .Rows.Count, Limit)
For this line:
..RowHeight = PossNewRowHeight / .Rows.Count

Greg

"Martin" wrote:

> Thanks a lot Greg,
>
> As you have mentioned the rows resize evenly. Is there a way to specify a
> minimum row height within the macro you provided. Or is it easier for me to
> run another macro resizing to the minimum row height?
>
> What do you think?
> --
> Many thanks for your help and kind regards,
>
> Martin
>
>
> "Greg Wilson" wrote:
>
> > I should also advise you that the macro will expand / contract the merged
> > rows evenly. I presume you also have nonmerged cells sharing the same rows.
> > Else, you wouldn't need to merge the range across rows but rather just
> > increase the row height. This being the case, you need to specify how you
> > want the rows to resize so they don't conflict with the nonmerged cells.
> >
> > Greg
> >
> >
> >
> > "Martin" wrote:
> >
> > > Hello there,
> > >
> > > I am using the "AutoFitMergedCellRowHeight" macro which is working fine for
> > > merged cells across several columns on one row such as range(A1:C1). But is
> > > does not seem to work when the merged cell is across several columns and also
> > > across several rows such as range(A1:C4). Does anyone have any idea of how to
> > > solve this problem.
> > >
> > > Any help much appreciated.
> > >
> > > --
> > > Regards,
> > >
> > > Martin
 |  Next  |  Last
Pages: 1 2
Prev: change Pivot table
Next: problems with a localconnection to a cub file