(Msg. 9) Posted: Fri Dec 05, 2008 1:36 pm
Post subject: RE: where text wraps in a cell, how can the row height be auto set [Login to view extended thread Info.] Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
I need to do exactly this in Excel 2007. I am not able to get the row auto
height to work. I can't even double click on the row header to make it
happen. Only dragging each individual row is working. I would greatly
appreciate some assistance getting this module to work in 2007. Thanks much!
"Greg Wilson" wrote:
> Paste the following to the worksheet's code module. The code assumes that
> each cell within A1:A10 is merged to adjacent columns as opposed to these
> cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
> merged etc. Change the range reference to suit. Can be a single cell.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim NewRwHt As Single
> Dim cWdth As Single, MrgeWdth As Single
> Dim r As Range, c As Range, cc As Range
> Dim ma As Range
>
> Set r = Range("A1:A10")
> If Not Intersect(Target, r) Is Nothing Then
> Set c = Target.Cells(1, 1)
> cWdth = c.ColumnWidth
> Set ma = c.MergeArea
> For Each cc In ma.Cells
> MrgeWdth = MrgeWdth + cc.ColumnWidth
> Next
> Application.ScreenUpdating = False
> ma.MergeCells = False
> c.ColumnWidth = MrgeWdth
> c.EntireRow.AutoFit
> NewRwHt = c.RowHeight
> c.ColumnWidth = cWdth
> ma.MergeCells = True
> ma.RowHeight = NewRwHt
> cWdth = 0: MrgeWdth = 0
> Application.ScreenUpdating = True
> End If
> End Sub
>
> Alternatively, size the column width of a single cell in the same row to the
> combined column widths of the merged range. Format the font, wraptext and
> alignment exactly the same except change the font colour to be the same as
> the background (to hide it). Enter a formula that references the active cell
> of the merged range (e.g. "=C10"). Use the worksheet_change event to force
> autofit of this cell. The merged cell range will then autofit along with it.
> This assumes it is columns that are merged. Use the same logic if rows are
> merged.
>
> Regards,
> Greg
>
>
>
>
> "Stephen Sandor" wrote:
>
> > I have merged a number of cells and included text that wraps in the cell. Is
> > it possible to set the cell so that the height is automatically adjusted to
> > the height of the text?
(Msg. 10) Posted: Sat Dec 06, 2008 10:58 am
Post subject: RE: where text wraps in a cell, how can the row height be auto set [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I'm not sure whats happened there but drop this in to the worksheet code
module:
Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Rows.EntireRow.AutoFit
End Sub
--------------------
every row you click should autofit!
(Msg. 11) Posted: Wed Feb 18, 2009 9:34 am
Post subject: RE: where text wraps in a cell, how can the row height be auto set [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I am having the same problem. Is it an Excel 2007 glitch?
Did you find a solution that works?
THX
Carol
"Datadonna" wrote:
> I need to do exactly this in Excel 2007. I am not able to get the row auto
> height to work. I can't even double click on the row header to make it
> happen. Only dragging each individual row is working. I would greatly
> appreciate some assistance getting this module to work in 2007. Thanks much!
>
>
>
> "Greg Wilson" wrote:
>
> > Paste the following to the worksheet's code module. The code assumes that
> > each cell within A1:A10 is merged to adjacent columns as opposed to these
> > cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
> > merged etc. Change the range reference to suit. Can be a single cell.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim NewRwHt As Single
> > Dim cWdth As Single, MrgeWdth As Single
> > Dim r As Range, c As Range, cc As Range
> > Dim ma As Range
> >
> > Set r = Range("A1:A10")
> > If Not Intersect(Target, r) Is Nothing Then
> > Set c = Target.Cells(1, 1)
> > cWdth = c.ColumnWidth
> > Set ma = c.MergeArea
> > For Each cc In ma.Cells
> > MrgeWdth = MrgeWdth + cc.ColumnWidth
> > Next
> > Application.ScreenUpdating = False
> > ma.MergeCells = False
> > c.ColumnWidth = MrgeWdth
> > c.EntireRow.AutoFit
> > NewRwHt = c.RowHeight
> > c.ColumnWidth = cWdth
> > ma.MergeCells = True
> > ma.RowHeight = NewRwHt
> > cWdth = 0: MrgeWdth = 0
> > Application.ScreenUpdating = True
> > End If
> > End Sub
> >
> > Alternatively, size the column width of a single cell in the same row to the
> > combined column widths of the merged range. Format the font, wraptext and
> > alignment exactly the same except change the font colour to be the same as
> > the background (to hide it). Enter a formula that references the active cell
> > of the merged range (e.g. "=C10"). Use the worksheet_change event to force
> > autofit of this cell. The merged cell range will then autofit along with it.
> > This assumes it is columns that are merged. Use the same logic if rows are
> > merged.
> >
> > Regards,
> > Greg
> >
> >
> >
> >
> > "Stephen Sandor" wrote:
> >
> > > I have merged a number of cells and included text that wraps in the cell. Is
> > > it possible to set the cell so that the height is automatically adjusted to
> > > the height of the text?
(Msg. 12) Posted: Wed Feb 25, 2009 7:57 am
Post subject: RE: where text wraps in a cell, how can the row height be auto set [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I am having this problem, also.
Since I don't know what the "worksheet code module" is, the above does not
help me.
Need more explaination.
"Datadonna" wrote:
> I need to do exactly this in Excel 2007. I am not able to get the row auto
> height to work. I can't even double click on the row header to make it
> happen. Only dragging each individual row is working. I would greatly
> appreciate some assistance getting this module to work in 2007. Thanks much!
>
>
>
> "Greg Wilson" wrote:
>
> > Paste the following to the worksheet's code module. The code assumes that
> > each cell within A1:A10 is merged to adjacent columns as opposed to these
> > cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
> > merged etc. Change the range reference to suit. Can be a single cell.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim NewRwHt As Single
> > Dim cWdth As Single, MrgeWdth As Single
> > Dim r As Range, c As Range, cc As Range
> > Dim ma As Range
> >
> > Set r = Range("A1:A10")
> > If Not Intersect(Target, r) Is Nothing Then
> > Set c = Target.Cells(1, 1)
> > cWdth = c.ColumnWidth
> > Set ma = c.MergeArea
> > For Each cc In ma.Cells
> > MrgeWdth = MrgeWdth + cc.ColumnWidth
> > Next
> > Application.ScreenUpdating = False
> > ma.MergeCells = False
> > c.ColumnWidth = MrgeWdth
> > c.EntireRow.AutoFit
> > NewRwHt = c.RowHeight
> > c.ColumnWidth = cWdth
> > ma.MergeCells = True
> > ma.RowHeight = NewRwHt
> > cWdth = 0: MrgeWdth = 0
> > Application.ScreenUpdating = True
> > End If
> > End Sub
> >
> > Alternatively, size the column width of a single cell in the same row to the
> > combined column widths of the merged range. Format the font, wraptext and
> > alignment exactly the same except change the font colour to be the same as
> > the background (to hide it). Enter a formula that references the active cell
> > of the merged range (e.g. "=C10"). Use the worksheet_change event to force
> > autofit of this cell. The merged cell range will then autofit along with it.
> > This assumes it is columns that are merged. Use the same logic if rows are
> > merged.
> >
> > Regards,
> > Greg
> >
> >
> >
> >
> > "Stephen Sandor" wrote:
> >
> > > I have merged a number of cells and included text that wraps in the cell. Is
> > > it possible to set the cell so that the height is automatically adjusted to
> > > the height of the text?
(Msg. 13) Posted: Wed Feb 25, 2009 11:59 am
Post subject: Re: where text wraps in a cell, how can the row height be auto set [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
(General, Regular and Standard modules all describe the same thing.)
Deb wrote:
>
> I am having this problem, also.
>
> Since I don't know what the "worksheet code module" is, the above does not
> help me.
>
> Need more explaination.
>
> "Datadonna" wrote:
>
> > I need to do exactly this in Excel 2007. I am not able to get the row auto
> > height to work. I can't even double click on the row header to make it
> > happen. Only dragging each individual row is working. I would greatly
> > appreciate some assistance getting this module to work in 2007. Thanks much!
> >
> >
> >
> > "Greg Wilson" wrote:
> >
> > > Paste the following to the worksheet's code module. The code assumes that
> > > each cell within A1:A10 is merged to adjacent columns as opposed to these
> > > cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
> > > merged etc. Change the range reference to suit. Can be a single cell.
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim NewRwHt As Single
> > > Dim cWdth As Single, MrgeWdth As Single
> > > Dim r As Range, c As Range, cc As Range
> > > Dim ma As Range
> > >
> > > Set r = Range("A1:A10")
> > > If Not Intersect(Target, r) Is Nothing Then
> > > Set c = Target.Cells(1, 1)
> > > cWdth = c.ColumnWidth
> > > Set ma = c.MergeArea
> > > For Each cc In ma.Cells
> > > MrgeWdth = MrgeWdth + cc.ColumnWidth
> > > Next
> > > Application.ScreenUpdating = False
> > > ma.MergeCells = False
> > > c.ColumnWidth = MrgeWdth
> > > c.EntireRow.AutoFit
> > > NewRwHt = c.RowHeight
> > > c.ColumnWidth = cWdth
> > > ma.MergeCells = True
> > > ma.RowHeight = NewRwHt
> > > cWdth = 0: MrgeWdth = 0
> > > Application.ScreenUpdating = True
> > > End If
> > > End Sub
> > >
> > > Alternatively, size the column width of a single cell in the same row to the
> > > combined column widths of the merged range. Format the font, wraptext and
> > > alignment exactly the same except change the font colour to be the same as
> > > the background (to hide it). Enter a formula that references the active cell
> > > of the merged range (e.g. "=C10"). Use the worksheet_change event to force
> > > autofit of this cell. The merged cell range will then autofit along with it.
> > > This assumes it is columns that are merged. Use the same logic if rows are
> > > merged.
> > >
> > > Regards,
> > > Greg
> > >
> > >
> > >
> > >
> > > "Stephen Sandor" wrote:
> > >
> > > > I have merged a number of cells and included text that wraps in the cell. Is
> > > > it possible to set the cell so that the height is automatically adjusted to
> > > > the height of the text?
(Msg. 14) Posted: Sun May 31, 2009 9:51 pm
Post subject: RE: where text wraps in a cell, how can the row height be auto set [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I got this code to work, thank t=you very much, I have a question however
which is:
I have a number of merged cell instances within the same worksheet and
workbook where I need this functionality. I cannot get this code to work for
additional instances. Can you help?
Thank you.
"Greg Wilson" wrote:
> Paste the following to the worksheet's code module. The code assumes that
> each cell within A1:A10 is merged to adjacent columns as opposed to these
> cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
> merged etc. Change the range reference to suit. Can be a single cell.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim NewRwHt As Single
> Dim cWdth As Single, MrgeWdth As Single
> Dim r As Range, c As Range, cc As Range
> Dim ma As Range
>
> Set r = Range("A1:A10")
> If Not Intersect(Target, r) Is Nothing Then
> Set c = Target.Cells(1, 1)
> cWdth = c.ColumnWidth
> Set ma = c.MergeArea
> For Each cc In ma.Cells
> MrgeWdth = MrgeWdth + cc.ColumnWidth
> Next
> Application.ScreenUpdating = False
> ma.MergeCells = False
> c.ColumnWidth = MrgeWdth
> c.EntireRow.AutoFit
> NewRwHt = c.RowHeight
> c.ColumnWidth = cWdth
> ma.MergeCells = True
> ma.RowHeight = NewRwHt
> cWdth = 0: MrgeWdth = 0
> Application.ScreenUpdating = True
> End If
> End Sub
>
> Alternatively, size the column width of a single cell in the same row to the
> combined column widths of the merged range. Format the font, wraptext and
> alignment exactly the same except change the font colour to be the same as
> the background (to hide it). Enter a formula that references the active cell
> of the merged range (e.g. "=C10"). Use the worksheet_change event to force
> autofit of this cell. The merged cell range will then autofit along with it.
> This assumes it is columns that are merged. Use the same logic if rows are
> merged.
>
> Regards,
> Greg
>
>
>
>
> "Stephen Sandor" wrote:
>
> > I have merged a number of cells and included text that wraps in the cell. Is
> > it possible to set the cell so that the height is automatically adjusted to
> > the height of the text?
(Msg. 15) Posted: Mon Jul 27, 2009 11:58 am
Post subject: RE: where text wraps in a cell, how can the row height be auto set [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"Greg Wilson" wrote:
> Paste the following to the worksheet's code module. The code assumes that
> each cell within A1:A10 is merged to adjacent columns as opposed to these
> cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
> merged etc. Change the range reference to suit. Can be a single cell.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim NewRwHt As Single
> Dim cWdth As Single, MrgeWdth As Single
> Dim r As Range, c As Range, cc As Range
> Dim ma As Range
>
> Set r = Range("A1:A10")
> If Not Intersect(Target, r) Is Nothing Then
> Set c = Target.Cells(1, 1)
> cWdth = c.ColumnWidth
> Set ma = c.MergeArea
> For Each cc In ma.Cells
> MrgeWdth = MrgeWdth + cc.ColumnWidth
> Next
> Application.ScreenUpdating = False
> ma.MergeCells = False
> c.ColumnWidth = MrgeWdth
> c.EntireRow.AutoFit
> NewRwHt = c.RowHeight
> c.ColumnWidth = cWdth
> ma.MergeCells = True
> ma.RowHeight = NewRwHt
> cWdth = 0: MrgeWdth = 0
> Application.ScreenUpdating = True
> End If
> End Sub
>
> Alternatively, size the column width of a single cell in the same row to the
> combined column widths of the merged range. Format the font, wraptext and
> alignment exactly the same except change the font colour to be the same as
> the background (to hide it). Enter a formula that references the active cell
> of the merged range (e.g. "=C10"). Use the worksheet_change event to force
> autofit of this cell. The merged cell range will then autofit along with it.
> This assumes it is columns that are merged. Use the same logic if rows are
> merged.
>
> Regards,
> Greg
>
>
>
>
> "Stephen Sandor" wrote:
>
> > I have merged a number of cells and included text that wraps in the cell. Is
> > it possible to set the cell so that the height is automatically adjusted to
> > the height of the text?
(Msg. 16) Posted: Mon Jul 27, 2009 12:01 pm
Post subject: RE: where text wraps in a cell, how can the row height be auto set [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I have a row with merged cells and I am trying to get the height to
automatically adjust. How do I get this to happen?
Linda B.
"Greg Wilson" wrote:
> Paste the following to the worksheet's code module. The code assumes that
> each cell within A1:A10 is merged to adjacent columns as opposed to these
> cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
> merged etc. Change the range reference to suit. Can be a single cell.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim NewRwHt As Single
> Dim cWdth As Single, MrgeWdth As Single
> Dim r As Range, c As Range, cc As Range
> Dim ma As Range
>
> Set r = Range("A1:A10")
> If Not Intersect(Target, r) Is Nothing Then
> Set c = Target.Cells(1, 1)
> cWdth = c.ColumnWidth
> Set ma = c.MergeArea
> For Each cc In ma.Cells
> MrgeWdth = MrgeWdth + cc.ColumnWidth
> Next
> Application.ScreenUpdating = False
> ma.MergeCells = False
> c.ColumnWidth = MrgeWdth
> c.EntireRow.AutoFit
> NewRwHt = c.RowHeight
> c.ColumnWidth = cWdth
> ma.MergeCells = True
> ma.RowHeight = NewRwHt
> cWdth = 0: MrgeWdth = 0
> Application.ScreenUpdating = True
> End If
> End Sub
>
> Alternatively, size the column width of a single cell in the same row to the
> combined column widths of the merged range. Format the font, wraptext and
> alignment exactly the same except change the font colour to be the same as
> the background (to hide it). Enter a formula that references the active cell
> of the merged range (e.g. "=C10"). Use the worksheet_change event to force
> autofit of this cell. The merged cell range will then autofit along with it.
> This assumes it is columns that are merged. Use the same logic if rows are
> merged.
>
> Regards,
> Greg
>
>
>
>
> "Stephen Sandor" wrote:
>
> > I have merged a number of cells and included text that wraps in the cell. Is
> > it possible to set the cell so that the height is automatically adjusted to
> > the height of the text?
All times are: Eastern Time (US & Canada) (change) Goto page Previous1, 2, 3
Page 2 of 3
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office