Automatically resizing cells

  • Thread starter Thread starter JJ
  • Start date Start date
J

JJ

Hi,

I am trying to figure out a way that would automatically resize cell height
once I enter additional information and part of the text disappears from the
screen.
Currently it appears that the only way to do that is to double click on the
bottom border of the cell I am on.

I am using MS Excel 2000.

Appreciate you feedback,

Jason
 
JJ said:
Hi,

I am trying to figure out a way that would automatically resize cell height
once I enter additional information and part of the text disappears from the
screen.
Click to expand...

A slight correction. I am looking for automatic resizing when I change the
width of the column.
 
Not quite automatic and not quite real time...

But how about having the rowheight adjust as soon as you select another cell?

If you like this idea, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this in:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Rows.AutoFit
End Sub

It does autofit all the rows, though.
 
I wonder if it would be possible to explicitly run a macro
to make Excel think it had not reset each line size after
resetting them.

You can select all cell Ctrl+A and
then double-click on any boundary between the row
heading numbers. Problem is that Excel recognizes
those as the correct row heights and will no longer automatically
try to adjust them.

The Event macro that Dave supplied nullifies that problem
by readjusting the row heights everytime a selection is
changed -- but the disadvantage is that like
any macro you lose the ability to use undo (Ctrl+Z)
 
Dave Peterson said:
Not quite automatic and not quite real time...

But how about having the rowheight adjust as soon as you select another cell?

If you like this idea, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this in:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Rows.AutoFit
End Sub

It does autofit all the rows, though.
Click to expand...

This works perfectly! Loosing the undo is a small price to pay.
Thank you both for your feedback. (:-)
 
Dave Peterson said:
Not quite automatic and not quite real time...

But how about having the rowheight adjust as soon as you select another cell?

If you like this idea, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this in:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Rows.AutoFit
End Sub

It does autofit all the rows, though.
Click to expand...

Dave,
The only problem that I am having is that merged cells are being resized.
Is there any work around that you can think of?
Jason
 
Hi
Please help
Which formule can I use to get the next day in B1
For example:
A1 = Monday
B1 = formule A1 + 1 = Tuesday
or
A1 = Wednesday
B1 = formule A1 + 1 = Thursday

A B
1 Monday (A1+1)

Regards

Hans
 
Merged cells aren't very nice.

Jim Rech wrote a macro called AutoFitMergedCellRowHeight that you may like:
http://groups.google.com/groups?threadm=e1$1uzL1BHA.1784@tkmsftngp05

And this is a thread that used Jim's code from a worksheet_change event.
http://groups.google.com/[email protected]

If you type the value into the merged cells, then I think it'll work ok.

If the value changes as the result of a formula, then you could tie it into the
worksheet_calculate event.

But I think I'd set up a worksheet range name that includes all those merged
cells. Then your code wouldn't have to go looking to find them. (It would be
slow to find--at least the first time (or subsequent times if you merge/unmerge
cells).)
 
MR. JJ,

I enter long text (2000+ characters) into worksheet cells (50-wide)
frequently and subsequently, manual Autofit-ting the given cells does
not provide sufficient height of the cells to show the full contents
thereof.

Would you please feedback with the practical experience of the given
macros ?

Regards.
 
TKT-Tang said:
MR. JJ,

I enter long text (2000+ characters) into worksheet cells (50-wide)
frequently and subsequently, manual Autofit-ting the given cells does
not provide sufficient height of the cells to show the full contents
thereof.

Would you please feedback with the practical experience of the given
macros ?

Regards.
Click to expand...

Greetings,

I entered the macro that Dave provided and the automatic resizing of single
cells throughout the document worked perfectly. The only problem I had was
with merged cells which would not get resized. I will try the work around
that he was good enough to propose and will let everyone know how it went.

Jason
 
I played around with some test data and this _seemed_ to work ok.

But test it much more thoroughly than I did.

In a General Module:

Option Explicit
Sub myAutoFitMergedCellRowHeight(myActiveCell As Range)

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

Dim myCell As Range
Dim myMergedCells As Range
Dim wks As Worksheet

Set wks = ThisWorkbook.Worksheets("sheet1")

On Error Resume Next
wks.Names("MyMergedCells").Delete
On Error GoTo 0

If myMergedCells Is Nothing Then
For Each myCell In wks.UsedRange.Cells
If myCell.MergeCells Then
If myCell.Address = myCell.MergeArea.Cells(1, 1).Address Then
If myMergedCells Is Nothing Then
Set myMergedCells = myCell
Else
Set myMergedCells = Union(myMergedCells, myCell)
End If
End If
End If
Next myCell
End If

If myMergedCells Is Nothing Then
'do nothing
Else
myMergedCells.Name = "'" & wks.Name & "'!MyMergedCells"
End If

End Sub

Then under the worksheet that you want this to happen (I used Sheet1 in the code
above), paste this:

Option Explicit
Private Sub Worksheet_Calculate()

Dim myCell As Range
Dim myMergedCells As Range

If Me.UsedRange.Cells.MergeCells = False Then
'no merged cells
Else
On Error Resume Next
Application.EnableEvents = False
Set myMergedCells = Me.Range("myMergedCells")
Application.EnableEvents = True
On Error GoTo 0

If myMergedCells Is Nothing Then
'time to rebuild list
Application.EnableEvents = False
Call MakeMergeCellRange
Application.EnableEvents = True
Set myMergedCells = Me.Range("myMergedCells")
End If
End If

Me.Rows.AutoFit

If myMergedCells Is Nothing Then
'do nothing
Else
Application.ScreenUpdating = False
For Each myCell In myMergedCells
Call myAutoFitMergedCellRowHeight(myCell)
Next myCell
Application.ScreenUpdating = True
End If

End Sub

Each time the worksheet calculates, it'll resize the rows. And it'll cycle
through the merged cells trying to autofit each of those, too.

If you decide you want to add more merged areas to the worksheet, you can delete
the name "MyMergedCells" via:

the Insert|Names|Define dialog.

A new name will get created with the next calculation and it'll include the
merged cells.

(a side benefit is that if you Edit|goto, select mymergedcells, you'll select
the merged cells.)

Now, honestly, I wouldn't ever use something like this. I'd resize manually and
be happy.
 
Dave Peterson said:
I played around with some test data and this _seemed_ to work ok.

But test it much more thoroughly than I did.

In a General Module:
Click to expand...

Something I am doing is wrong. I tried to look up the definition of teh
General Module and could not find it so I assumed that it was the View Code
area of a sheet. I thus opened up a new document and right clicked on the
Sheet1 tab and copied the code below but that would not anything to the
merged cells.
For all the cells I am testing, I have checked the Wrap Text flag.
Actually it does appear as if it is resizing the single cells.
Do I need to add this code to the previous one?
I am lost. (:-(
 
Get to the VBE.
Hit Ctrl-r to see the project explorer
and select your project in the project explorer

Then click on Insert|Module (on the menubar).

Paste the code there.

You could actually leave it behind the worksheet (I don't think I would). By
putting it in a general/regular module (as opposed to a class module), it makes
it easier for another procedure to call it. (Say the same type of function
behind another worksheet.)
 
Dave Peterson said:
Get to the VBE.
Hit Ctrl-r to see the project explorer
and select your project in the project explorer

Then click on Insert|Module (on the menubar).

Paste the code there.
Click to expand...

I got it to work but only when I hit the "run sub / user form" button.
There is no way to do this automatically upon moving from a cell, similar to
the way it worked the first time without the merged cells complexity?
I know my insistence at automating this resizing sounds absurd but this
spreadsheet has become a bit unwieldy in size and with multiple folks
updating it I would to keep it neat without having to go in manually every
time someone updates it.
If this doesn't work I'll be more than happy with the original solution.
 
I thought that you were using formulas to have the cell's value changed (I think
I thought that???).

But for whatever reason, I chose worksheet_calculate as the event.

You could put that code under:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

But it'll fire whenever you change to a different cell--which will probably be
lots of unnecessary times.

Maybe you could use both the _calculate and _change events to fix both the
calculated cells and the manually changed cells.


Dave Peterson said:
Get to the VBE.
Hit Ctrl-r to see the project explorer
and select your project in the project explorer

Then click on Insert|Module (on the menubar).

Paste the code there.
Click to expand...

I got it to work but only when I hit the "run sub / user form" button.
There is no way to do this automatically upon moving from a cell, similar to
the way it worked the first time without the merged cells complexity?
I know my insistence at automating this resizing sounds absurd but this
spreadsheet has become a bit unwieldy in size and with multiple folks
updating it I would to keep it neat without having to go in manually every
time someone updates it.
If this doesn't work I'll be more than happy with the original solution.
Click to expand...
 
Back
Top