Prev: Vlookup
Next: Setting up an electrical register in Excel with expiry dates
From: Broughan on 15 Apr 2010 04:26
It seems pretty silly that this is not intuitive. One way I know is highlight
all the rows, then on the left hand side of the spreadsheet, where the rows
are numbered double click with the left mouse button on the line between two
of the rows, and bingo :)


"Elso" wrote:

> Hello
>
> Would somebody mind to please explain this auto set row height in very
> beginner terms? I am not in any way Excel Savvy. But I do have one very
> simple project in Excel I am trying to complete. Very simply, I have a
> "master" workbook that is to update other workbooks with exact text entered
> in the master. No calculation or other fancy stuff, just the means of
> entering text once and having it be copied to other workbooks. I am learning
> as I go and for the most part I have a good handle on this simple process.
> There are areas where an auto height adjustment of both the source field and
> the targeted fields would be very useful. Cells do not need to be merged
> cells. Simple copy/paste example would be greatly appreciated.
> For an example that I could copy/past:
> the source cell could be A19 and the target cell in a different workbook
> could be B37.
>
> Thank you for anyone who wishes to take the time to explain how this is put
> into play
> Nelson
>
> "Gord Dibben" wrote:
>
> > If destination cell is set to wrap text and autofit you still have to
> > d-click on the bottom edge of the row header.
> >
> > I don't know if the results of a formula will trigger the autofit.
> >
> > Won't on my 2003 version.
> >
> >
> > Gord
> >
> > On Fri, 1 Aug 2008 14:16:03 -0700, Terry H.
> > <TerryH(a)discussions.microsoft.com> wrote:
> >
> > >Thanks. That makes sense, and I can probably set things up so there is a
> > >single cell to a single cell linkage. So I think my question is really more
> > >about the linkage between cells instead of the merging cells.
> > >
> > >Let's say I have a worksheet tab for data entry (sheet1) that has a cell
> > >linked to a cell in another tab (sheet2) - one cell linking to one cell. The
> > >destination cell (in sheet2) will not autofit to the wrapped text. Instead,
> > >I have to manually resize the row to see all of the entered text. Is there a
> > >way to set it up so that if data is entered into the cell in sheet1 the cell
> > >height in sheet2 automatically expands to accommodate all of the text without
> > >a manual adjustment?
> > >
> > >"Gord Dibben" wrote:
> > >
> > >> If you are linking a single cell in sheet1 to a merged cell on another sheet
> > >> the single cell is not merged so Greg's code will not work on it.
> > >>
> > >> Regular wrap text and autofit should work on single cells in sheet1
> > >>
> > >>
> > >> Gord Dibben MS Excel MVP
> > >>
> > >> On Fri, 1 Aug 2008 08:23:01 -0700, Terry H. <Terry
> > >> H.(a)discussions.microsoft.com> wrote:
> > >>
> > >> >Hi Greg,
> > >> >
> > >> >Great code! Works like a charm. I found an area where this does not appear
> > >> >to work. Any help you can provide would be greatly appreciated. I have an
> > >> >Excel spreadsheet where information is entered in cells on one tab. Those
> > >> >cells are linked to merged cells on another tab. This code does not appear
> > >> >to work when applied to the merged cells that are linked. Any way around
> > >> >this? Thanks,
> > >> >
> > >> >Terry
> > >> >
> > >> >"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?
> > >>
> > >>
> >
> >
From: SG on 7 May 2010 13:04
Thanks Ruth. I tried this and it worked for me. I unmerged the cells and
then double clicked for auto row height and it worked.

"Ruth" wrote:

> I had the same issue. I found a work around for my situation. I made the
> column as wider instead of merging cells, then when you select wrap text the
> automatic row height works. It may not work for all but does for me.
>
> "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?
From: Angie on 10 May 2010 14:57
I need help for a very similar problem:

NO merged cells
NO specialized formatting, and format is consistent across spreadsheet
cells are set to "wrap text"

When I click on "autofit row height", 90% of the rows become the correct
height, while 10% do not. I have a few thousand rows of data, and 26 columns
with varying amounts of text in them. Sometimes the row becomes too short,
sometimes too tall, and it can happen no matter how much or how little text
is in it.

Can I repeat that I DO NOT have any merged cells? I have searched online
for hours and no one seems to have an explanation or a solution.
From: thepoundster on 4 Jun 2010 04:22
Thanks the Code Cage Team! I wanted cells i was typing in to autofit and the
code you supplied worked a treat. I was originally typing into merged cells
for which the code didn't work but after some re-arranging i was able to get
rid of the merged cells. For future reference is there a way of doing this on
merged cells?

"The Code Cage Team" wrote:

>
> 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!
>
>
> --
> The Code Cage Team
>
> Regards,
> The Code Cage Team
> 'The Code Cage' (http://www.thecodecage.com)
> ------------------------------------------------------------------------
> The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=37732
>
>
From: Gord Dibben on 4 Jun 2010 20:18
You can Autofit rows with merged cells only by using VBA.

One of the many drawbacks to using merged cells.

If you want to go that route...............

See google search thread for code by Greg Wilson. Watch out for word wrap
in the URL which is all one line.

http://groups.google.com/group/microsoft.public.excel.misc/browse_frm/thread/2293ac4e6373c4d3/ca1c160cbeb27874?lnk=st&q=&rnum=4#ca1c160cbeb27874

Note..........this is sheet event code and runs when a cell is changed
manually.

To store the code, right-click on your sheet tab and "View Code"

Copy/paste the code into that sheet module.

Alt + q to return to Excel.

Rows must be formatted to Autofit and cells formatted to Wrap Text


Gord Dibben MS Excel MVP

On Fri, 4 Jun 2010 01:22:44 -0700, thepoundster
<thepoundster(a)discussions.microsoft.com> wrote:

>Thanks the Code Cage Team! I wanted cells i was typing in to autofit and the
>code you supplied worked a treat. I was originally typing into merged cells
>for which the code didn't work but after some re-arranging i was able to get
>rid of the merged cells. For future reference is there a way of doing this on
>merged cells?
>
>"The Code Cage Team" wrote:
>
>>
>> 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!
>>
>>
>> --
>> The Code Cage Team
>>
>> Regards,
>> The Code Cage Team
>> 'The Code Cage' (http://www.thecodecage.com)
>> ------------------------------------------------------------------------
>> The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
>> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=37732
>>
>>

 | 
Pages: 1
Prev: Vlookup
Next: Setting up an electrical register in Excel with expiry dates