Auto row height for merged cells

[RealmSteel]

I've got a sheet that has a bunch of user inputted information in the top
portion.
The lower portion is for a message.
A lot like a Fax cover letter.

For each row in the lower portion with the meassge, I have columns B thru L
merged. I don't expect the sheet to be more than 3 or 4 pages long max when
printed.

I am looking to create some code that will automatically adjust the row
height for the merged cells if the user types a sentance too long.

Another problem I have is if I merge a bunch of rows (say 50), it will print
these rows even though no data is in them. I need to prevent that from
happening.
I only want ot print as many pages as needs with out waste.

I'm very new to VBA, but found this link during a search.
http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/a5cbe0ee8e6c2a10/93c6bca447bd8902#93c6bca447bd8902

I haven't tried it yet, but I know I would need to modify it to search for
all cells below say 16 and adjust row height as needed.

Is there any newer code out there for doing this?
Also, is there a way to do this automatically so the row is adjusted right
when the user hits enter or navigates to a different cell?

Is there a way to automatically adjust the print area without having to
create a button?
I created a button to print the log for these sheets, but was hoping to be
able to just allow the user to use the normal printer icon.

Thanks,
Rich

[Jim Jackson]

'The line:
Rows.autofit
' should give you the proper row height.

You can always specify the row number if desired:
Rows(12).Autofit

--
Best wishes,

Jim

[RealmSteel]

Jim,

I'm not sure why that is not working for me.

Since I'm new to VBA, can you provide the whole routine I can cut and
paste into my worksheet code?
I tried:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Rows.autofit
End Sub

I really don't understand what the 1st line actually does yet.
I assume it looks at a current selection when the enter key is pressed
or you navigate away from it.

I'll be getting a book on VBA if I don't get one for Christmas.
For now I am learning as I go.

On Dec 11, 10:38 am, Jim Jackson

<JimJack...@discussions.microsoft.com> wrote:
> 'The line:
> Rows.autofit
> ' should give you the proper row height.
>
> You can always specify the row number if desired:
> Rows(12).Autofit
>
> --
> Best wishes,
>
> Jim
>
>
>
> "RealmSteel" wrote:
> > I've got a sheet that has a bunch of user inputted information in the top
> > portion.
> > The lower portion is for a message.
> > A lot like a Fax cover letter.
>
> > For each row in the lower portion with the meassge, I have columns B thru L
> > merged. I don't expect the sheet to be more than 3 or 4 pages long max when
> > printed.
>
> > I am looking to create some code that will automatically adjust the row
> > height for the merged cells if the user types a sentance too long.
>
> > Another problem I have is if I merge a bunch of rows (say 50), it will print
> > these rows even though no data is in them. I need to prevent that from
> > happening.
> > I only want ot print as many pages as needs with out waste.
>
> > I'm very new to VBA, but found this link during a search.

> >http://groups.google.com/group/microsoft.public.excel.programming/bro...

>
> > I haven't tried it yet, but I know I would need to modify it to search for
> > all cells below say 16 and adjust row height as needed.
>
> > Is there any newer code out there for doing this?
> > Also, is there a way to do this automatically so the row is adjusted right
> > when the user hits enter or navigates to a different cell?
>
> > Is there a way to automatically adjust the print area without having to
> > create a button?
> > I created a button to print the log for these sheets, but was hoping to be
> > able to just allow the user to use the normal printer icon.
>
> > Thanks,

> > Rich- Hide quoted text -- Show quoted text -

[Jim Jackson]

When I formatted some cells as "Wrap Text" and typed a sentence in one, made
sure only one line of text was showing and clicked "Enter", the cells resized
to show the complete text.

Are your cells already formatted to allow Wrapping of Text?

[RealmSteel]

Jim,

I'm obviously doing something wrong.

I just created a new workbook and inserted the following code into
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Rows.AutoFit
End Sub

I then merged 3 columns (B, C & D) in row 3, formatted them for left
justification and Wrap text.
I copy and pasted these cells for another 10 rows.

I verified the formatting for all the merged cells is Wrap Text.

I typed into one of the cells a line longer than the merged cell width
and nothing happened.

My version os Excell 2003 if that matters.

I did notice with the code from the link I provided in my first post,
the cell will auto adjust if I navigate back to it and then away.
All I have to do is select it and then select another cell. The row
will automatically adjust.
I'd like it to auto adjust as a soon as I navigate away.

Here is what the code looks like right now:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

Thanks,
Rich

On Dec 11, 1:48 pm, Jim Jackson <JimJack...@discussions.microsoft.com>
wrote:

> When I formatted some cells as "Wrap Text" and typed a sentence in one, made
> sure only one line of text was showing and clicked "Enter", the cells resized
> to show the complete text.
>
> Are your cells already formatted to allow Wrapping of Text?
>
> --
> Best wishes,
>
> Jim
>
>

text -- Hide quoted text -- Show quoted text -

[Jim Jackson]

I'm on my way out of the office but will get back with you ASAP when I get
home. I want to know why this is not working and am determined to find out
why.
--
Best wishes,

Jim