Auto size help

I have a feature in my workbook that produces a report view on one worksheet
by copying certain data fields into labelled cells. One receiving cell is
comprised of merged cells - when it receives text/data only some of it is
visible as the cell is too small - tried several things to no avail. Is
there a for the labelled cell to auto fit the the received data or is this
limited by excel?

Tia

Merged cells don't autofit. You might look at this code previously posted
by Jim Rech:

Jim Rech

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

--
Regards,
Tom Ogilvy


"sacrum" <[email protected]> wrote in message
news:[email protected]...
> I have a feature in my workbook that produces a report view on one
worksheet
> by copying certain data fields into labelled cells. One receiving cell is
> comprised of merged cells - when it receives text/data only some of it is
> visible as the cell is too small - tried several things to no avail. Is
> there a for the labelled cell to auto fit the the received data or is this
> limited by excel?
>
> Tia
>
>

Hi Tom,

I'm having rpoblems with the script....

My worksheet has a sub called Form which extracts choice and put onto form.

The merged cells are in worksheet set as wrep and are labelled RepRiskDetail

Would you kindly help me put that context into the script you posted?
Ideally I would want this function within the sub form.

Chris

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Merged cells don't autofit. You might look at this code previously posted
> by Jim Rech:
>
> Jim Rech
>
> Sub AutoFitMergedCellRowHeight()
> Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
> Dim CurrCell As Range
> Dim ActiveCellWidth As Single, PossNewRowHeight As Single
> If ActiveCell.MergeCells Then
> With ActiveCell.MergeArea
> If .Rows.Count = 1 And .WrapText = True Then
> Application.ScreenUpdating = False
> CurrentRowHeight = .RowHeight
> ActiveCellWidth = ActiveCell.ColumnWidth
> For Each CurrCell In Selection
> MergedCellRgWidth = CurrCell.ColumnWidth + _
> MergedCellRgWidth
> Next
> .MergeCells = False
> .Cells(1).ColumnWidth = MergedCellRgWidth
> .EntireRow.AutoFit
> PossNewRowHeight = .RowHeight
> .Cells(1).ColumnWidth = ActiveCellWidth
> .MergeCells = True
> .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
> CurrentRowHeight, PossNewRowHeight)
> End If
> End With
> End If
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "sacrum" <[email protected]> wrote in message
> news:[email protected]...
>> I have a feature in my workbook that produces a report view on one
> worksheet
>> by copying certain data fields into labelled cells. One receiving cell
>> is
>> comprised of merged cells - when it receives text/data only some of it is
>> visible as the cell is too small - tried several things to no avail. Is
>> there a for the labelled cell to auto fit the the received data or is
>> this
>> limited by excel?
>>
>> Tia
>>
>>
>
>

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
' add this line:
Range("RepRiskDetail").Select
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

--
regards,
Tom Ogilvy


"sacrum" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom,
>
> I'm having rpoblems with the script....
>
> My worksheet has a sub called Form which extracts choice and put onto
form.
>
> The merged cells are in worksheet set as wrep and are labelled
RepRiskDetail
>
> Would you kindly help me put that context into the script you posted?
> Ideally I would want this function within the sub form.
>
> Chris
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Merged cells don't autofit. You might look at this code previously
posted
> > by Jim Rech:
> >
> > Jim Rech
> >
> > Sub AutoFitMergedCellRowHeight()
> > Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
> > Dim CurrCell As Range
> > Dim ActiveCellWidth As Single, PossNewRowHeight As Single
> > If ActiveCell.MergeCells Then
> > With ActiveCell.MergeArea
> > If .Rows.Count = 1 And .WrapText = True Then
> > Application.ScreenUpdating = False
> > CurrentRowHeight = .RowHeight
> > ActiveCellWidth = ActiveCell.ColumnWidth
> > For Each CurrCell In Selection
> > MergedCellRgWidth = CurrCell.ColumnWidth + _
> > MergedCellRgWidth
> > Next
> > .MergeCells = False
> > .Cells(1).ColumnWidth = MergedCellRgWidth
> > .EntireRow.AutoFit
> > PossNewRowHeight = .RowHeight
> > .Cells(1).ColumnWidth = ActiveCellWidth
> > .MergeCells = True
> > .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
> > CurrentRowHeight, PossNewRowHeight)
> > End If
> > End With
> > End If
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "sacrum" <[email protected]> wrote in message
> > news:[email protected]...
> >> I have a feature in my workbook that produces a report view on one
> > worksheet
> >> by copying certain data fields into labelled cells. One receiving cell
> >> is
> >> comprised of merged cells - when it receives text/data only some of it
is
> >> visible as the cell is too small - tried several things to no avail.
Is
> >> there a for the labelled cell to auto fit the the received data or is
> >> this
> >> limited by excel?
> >>
> >> Tia
> >>
> >>
> >
> >
>
>

Thanjks Tom - all is working well now.


"Tom Ogilvy" <[email protected]> wrote in message
news:OMa%[email protected]...
> Sub AutoFitMergedCellRowHeight()
> Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
> Dim CurrCell As Range
> Dim ActiveCellWidth As Single, PossNewRowHeight As Single
> ' add this line:
> Range("RepRiskDetail").Select
> If ActiveCell.MergeCells Then
> With ActiveCell.MergeArea
> If .Rows.Count = 1 And .WrapText = True Then
> Application.ScreenUpdating = False
> CurrentRowHeight = .RowHeight
> ActiveCellWidth = ActiveCell.ColumnWidth
> For Each CurrCell In Selection
> MergedCellRgWidth = CurrCell.ColumnWidth + _
> MergedCellRgWidth
> Next
> .MergeCells = False
> .Cells(1).ColumnWidth = MergedCellRgWidth
> .EntireRow.AutoFit
> PossNewRowHeight = .RowHeight
> .Cells(1).ColumnWidth = ActiveCellWidth
> .MergeCells = True
> .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
> CurrentRowHeight, PossNewRowHeight)
> End If
> End With
> End If
> End Sub
>
> --
> regards,
> Tom Ogilvy
>
>
> "sacrum" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Tom,
>>
>> I'm having rpoblems with the script....
>>
>> My worksheet has a sub called Form which extracts choice and put onto
> form.
>>
>> The merged cells are in worksheet set as wrep and are labelled
> RepRiskDetail
>>
>> Would you kindly help me put that context into the script you posted?
>> Ideally I would want this function within the sub form.
>>
>> Chris
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > Merged cells don't autofit. You might look at this code previously
> posted
>> > by Jim Rech:
>> >
>> > Jim Rech
>> >
>> > Sub AutoFitMergedCellRowHeight()
>> > Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
>> > Dim CurrCell As Range
>> > Dim ActiveCellWidth As Single, PossNewRowHeight As Single
>> > If ActiveCell.MergeCells Then
>> > With ActiveCell.MergeArea
>> > If .Rows.Count = 1 And .WrapText = True Then
>> > Application.ScreenUpdating = False
>> > CurrentRowHeight = .RowHeight
>> > ActiveCellWidth = ActiveCell.ColumnWidth
>> > For Each CurrCell In Selection
>> > MergedCellRgWidth = CurrCell.ColumnWidth + _
>> > MergedCellRgWidth
>> > Next
>> > .MergeCells = False
>> > .Cells(1).ColumnWidth = MergedCellRgWidth
>> > .EntireRow.AutoFit
>> > PossNewRowHeight = .RowHeight
>> > .Cells(1).ColumnWidth = ActiveCellWidth
>> > .MergeCells = True
>> > .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
>> > CurrentRowHeight, PossNewRowHeight)
>> > End If
>> > End With
>> > End If
>> > End Sub
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "sacrum" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> I have a feature in my workbook that produces a report view on one
>> > worksheet
>> >> by copying certain data fields into labelled cells. One receiving
>> >> cell
>> >> is
>> >> comprised of merged cells - when it receives text/data only some of it
> is
>> >> visible as the cell is too small - tried several things to no avail.
> Is
>> >> there a for the labelled cell to auto fit the the received data or is
>> >> this
>> >> limited by excel?
>> >>
>> >> Tia
>> >>
>> >>
>> >
>> >
>>
>>
>
>