|
Autosize cells as data is entered
Hello.
I have my cells set to Wrap & the row is set to Auto-Fit. The "Number" is set to General. When I add text, it wraps, however, the cell-size is not changing! What do I have to do to get the cell (or row height) to grow as text is added? -- Carol |
Autosize cells as data is entered
I'm betting you have some "merged cells" which won't Autofit without VBA code.
If this is the case and you're interested in the code, post back. Gord Dibben MS Excel MVP On Thu, 22 Feb 2007 10:49:13 -0800, Carol wrote: Hello. I have my cells set to Wrap & the row is set to Auto-Fit. The "Number" is set to General. When I add text, it wraps, however, the cell-size is not changing! What do I have to do to get the cell (or row height) to grow as text is added? |
Autosize cells as data is entered
Nevermind! Just realized my cells are merged - therefore, none of the
solutions I was trying will work. -- Carol "Carol" wrote: Hello. I have my cells set to Wrap & the row is set to Auto-Fit. The "Number" is set to General. When I add text, it wraps, however, the cell-size is not changing! What do I have to do to get the cell (or row height) to grow as text is added? -- Carol |
Autosize cells as data is entered
It is the case! I think we were both responding to my post at the same
time.. I would love the VBA code - though a novice at that. HOWEVER - if it's easier than completely re-doing my form, I'll be happy to take a stab at it! Thank you Gord! -- Carol "Gord Dibben" wrote: I'm betting you have some "merged cells" which won't Autofit without VBA code. If this is the case and you're interested in the code, post back. Gord Dibben MS Excel MVP On Thu, 22 Feb 2007 10:49:13 -0800, Carol wrote: Hello. I have my cells set to Wrap & the row is set to Auto-Fit. The "Number" is set to General. When I add text, it wraps, however, the cell-size is not changing! What do I have to do to get the cell (or row height) to grow as text is added? |
Autosize cells as data is entered
I avoid meged cells like the plague simply due to the many problems caused by
these. But............................ Here is event code from Greg Wilson. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText 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 With End Sub This is sheet event code. Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. As you enter data the rows will autofit. Gord On Thu, 22 Feb 2007 11:42:45 -0800, Carol wrote: It is the case! I think we were both responding to my post at the same time.. I would love the VBA code - though a novice at that. HOWEVER - if it's easier than completely re-doing my form, I'll be happy to take a stab at it! Thank you Gord! |
Autosize cells as data is entered
You (and Greg Wilson)...... BRILLIANT! Worked beautifully!
Thank you for help - and perfect instruction! -- Carol "Gord Dibben" wrote: I avoid meged cells like the plague simply due to the many problems caused by these. But............................ Here is event code from Greg Wilson. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText 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 With End Sub This is sheet event code. Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. As you enter data the rows will autofit. Gord On Thu, 22 Feb 2007 11:42:45 -0800, Carol wrote: It is the case! I think we were both responding to my post at the same time.. I would love the VBA code - though a novice at that. HOWEVER - if it's easier than completely re-doing my form, I'll be happy to take a stab at it! Thank you Gord! |
Autosize cells as data is entered
Hey Gord,
I have a similar problem to the one you addressed in your post. When I highlight all the rows then select 'format', 'AutoFit Row Height' it will make the adjustments. However, once new text is added (actually, eveything in this tab is being controlled by a formula) the rows do not change height to accomodate the new text. I do have a couple merged cells, but they are at the very top and are not part of the rows that I am trying to use AutoFit on. I inserted the VBA code you pasted just in case I have merged cells, but that didn't help. This is the only other unique thing I can think of that may be causing it: in the cells of one column I have the values of multiple texts being returned on separate lines (e.g. one of the cells has the formula K14&CHAR(10)&L14&CHAR(10)&M14&CHAR(10)&N14). Can you think of any reason why AutoFit wont work? "Gord Dibben" wrote: I avoid meged cells like the plague simply due to the many problems caused by these. But............................ Here is event code from Greg Wilson. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText 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 With End Sub This is sheet event code. Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. As you enter data the rows will autofit. Gord On Thu, 22 Feb 2007 11:42:45 -0800, Carol wrote: It is the case! I think we were both responding to my post at the same time.. I would love the VBA code - though a novice at that. HOWEVER - if it's easier than completely re-doing my form, I'll be happy to take a stab at it! Thank you Gord! |
Autosize cells as data is entered
Even without merged cells Excel doen't seem to like too many linefeeds and will
not autofit after a certain point. I tried your example formula in A14 with row set to Autofit. Entry of text in K14 and L14 autofit but entries in M14 and N14 did not increase row height. I don't know of a workaround. Gord Dibben MS Excel MVP On Mon, 14 Jul 2008 14:29:01 -0700, DoubleZ wrote: Hey Gord, I have a similar problem to the one you addressed in your post. When I highlight all the rows then select 'format', 'AutoFit Row Height' it will make the adjustments. However, once new text is added (actually, eveything in this tab is being controlled by a formula) the rows do not change height to accomodate the new text. I do have a couple merged cells, but they are at the very top and are not part of the rows that I am trying to use AutoFit on. I inserted the VBA code you pasted just in case I have merged cells, but that didn't help. This is the only other unique thing I can think of that may be causing it: in the cells of one column I have the values of multiple texts being returned on separate lines (e.g. one of the cells has the formula K14&CHAR(10)&L14&CHAR(10)&M14&CHAR(10)&N14). Can you think of any reason why AutoFit wont work? "Gord Dibben" wrote: I avoid meged cells like the plague simply due to the many problems caused by these. But............................ Here is event code from Greg Wilson. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText 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 With End Sub This is sheet event code. Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. As you enter data the rows will autofit. Gord On Thu, 22 Feb 2007 11:42:45 -0800, Carol wrote: It is the case! I think we were both responding to my post at the same time.. I would love the VBA code - though a novice at that. HOWEVER - if it's easier than completely re-doing my form, I'll be happy to take a stab at it! Thank you Gord! |
Autosize cells as data is entered
Does anyone know how to modify this code so that it will work in a protected
sheet? "Gord Dibben" wrote: I avoid meged cells like the plague simply due to the many problems caused by these. But............................ Here is event code from Greg Wilson. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText 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 With End Sub This is sheet event code. Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. As you enter data the rows will autofit. Gord On Thu, 22 Feb 2007 11:42:45 -0800, Carol wrote: It is the case! I think we were both responding to my post at the same time.. I would love the VBA code - though a novice at that. HOWEVER - if it's easier than completely re-doing my form, I'll be happy to take a stab at it! Thank you Gord! |
Autosize cells as data is entered
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range On Error GoTo endit Application.EnableEvents = False With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 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 With endit: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord On Wed, 15 Oct 2008 16:35:03 -0700, Brian wrote: Does anyone know how to modify this code so that it will work in a protected sheet? "Gord Dibben" wrote: I avoid meged cells like the plague simply due to the many problems caused by these. But............................ Here is event code from Greg Wilson. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText 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 With End Sub This is sheet event code. Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. As you enter data the rows will autofit. Gord On Thu, 22 Feb 2007 11:42:45 -0800, Carol wrote: It is the case! I think we were both responding to my post at the same time.. I would love the VBA code - though a novice at that. HOWEVER - if it's easier than completely re-doing my form, I'll be happy to take a stab at it! Thank you Gord! |
Autosize cells as data is entered
Thank you, Gord!
Note that I did have a slight issue where the resized cells were becoming locked after editing, but I stole a line of code (ma.Locked = False) from another post, and that seems to solve the issue. Thanks again. "Gord Dibben" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range On Error GoTo endit Application.EnableEvents = False With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 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 With endit: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord |
Autosize cells as data is entered
Good to hear
Thanks for the feedback On Thu, 16 Oct 2008 06:31:01 -0700, Brian wrote: Thank you, Gord! Note that I did have a slight issue where the resized cells were becoming locked after editing, but I stole a line of code (ma.Locked = False) from another post, and that seems to solve the issue. Thanks again. "Gord Dibben" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range On Error GoTo endit Application.EnableEvents = False With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 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 With endit: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord |
Autosize cells as data is entered
will this work for data that has already been entered into my sheet or will
this only work on a new sheet and any data entered after it is applied? "Gord Dibben" wrote: Good to hear Thanks for the feedback On Thu, 16 Oct 2008 06:31:01 -0700, Brian wrote: Thank you, Gord! Note that I did have a slight issue where the resized cells were becoming locked after editing, but I stole a line of code (ma.Locked = False) from another post, and that seems to solve the issue. Thanks again. "Gord Dibben" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range On Error GoTo endit Application.EnableEvents = False With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 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 With endit: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord |
Autosize cells as data is entered
Only works when entering new data or editing existing data.
Gord On Mon, 8 Jun 2009 10:45:01 -0700, brianna3 wrote: will this work for data that has already been entered into my sheet or will this only work on a new sheet and any data entered after it is applied? "Gord Dibben" wrote: Good to hear Thanks for the feedback On Thu, 16 Oct 2008 06:31:01 -0700, Brian wrote: Thank you, Gord! Note that I did have a slight issue where the resized cells were becoming locked after editing, but I stole a line of code (ma.Locked = False) from another post, and that seems to solve the issue. Thanks again. "Gord Dibben" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range On Error GoTo endit Application.EnableEvents = False With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 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 With endit: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord |
Autosize cells as data is entered
I added the VBA to an existing worksheet and although it didn't expand my
rows as soon as I added the code, as soon as I typed into the cell again the row expanded and stayed expanded. I hope that helps! Thanks for the code - and the specific instructions where to add it (rt-clk on the tab). I never knew that and always wondered how code was being added!! Thanks again! "Gord Dibben" wrote: Only works when entering new data or editing existing data. Gord On Mon, 8 Jun 2009 10:45:01 -0700, brianna3 wrote: will this work for data that has already been entered into my sheet or will this only work on a new sheet and any data entered after it is applied? "Gord Dibben" wrote: Good to hear Thanks for the feedback On Thu, 16 Oct 2008 06:31:01 -0700, Brian wrote: Thank you, Gord! Note that I did have a slight issue where the resized cells were becoming locked after editing, but I stole a line of code (ma.Locked = False) from another post, and that seems to solve the issue. Thanks again. "Gord Dibben" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range On Error GoTo endit Application.EnableEvents = False With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 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 With endit: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord |
Autosize cells as data is entered
To autofit existing rows after you added the code, select a cell and F2 then
ENTER This is same as typing in the cell. Gord On Thu, 18 Mar 2010 08:50:01 -0700, donna-LexusWebs wrote: I added the VBA to an existing worksheet and although it didn't expand my rows as soon as I added the code, as soon as I typed into the cell again the row expanded and stayed expanded. I hope that helps! Thanks for the code - and the specific instructions where to add it (rt-clk on the tab). I never knew that and always wondered how code was being added!! Thanks again! "Gord Dibben" wrote: Only works when entering new data or editing existing data. Gord On Mon, 8 Jun 2009 10:45:01 -0700, brianna3 wrote: will this work for data that has already been entered into my sheet or will this only work on a new sheet and any data entered after it is applied? "Gord Dibben" wrote: Good to hear Thanks for the feedback On Thu, 16 Oct 2008 06:31:01 -0700, Brian wrote: Thank you, Gord! Note that I did have a slight issue where the resized cells were becoming locked after editing, but I stole a line of code (ma.Locked = False) from another post, and that seems to solve the issue. Thanks again. "Gord Dibben" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range On Error GoTo endit Application.EnableEvents = False With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 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 With endit: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord |
how to auto expand linked data from another cell?
Hi,
Although this code works with cells where data is actually in it, I need it to work where the cell contains text that has been linked from another sheet. Can anyone help? thanks donna-LexusWebs wrote: I added the VBA to an existing worksheet and although it did not expand myrows 18-Mar-10 I added the VBA to an existing worksheet and although it did not expand my rows as soon as I added the code, as soon as I typed into the cell again the row expanded and stayed expanded. I hope that helps! Thanks for the code - and the specific instructions where to add it (rt-clk on the tab). I never knew that and always wondered how code was being added!! Thanks again! "Gord Dibben" wrote: Previous Posts In This Thread: On Thursday, February 22, 2007 1:49 PM Caro wrote: Autosize cells as data is entered Hello. I have my cells set to Wrap & the row is set to Auto-Fit. The "Number" is set to General. When I add text, it wraps, however, the cell-size is not changing! What do I have to do to get the cell (or row height) to grow as text is added? -- Carol On Thursday, February 22, 2007 2:30 PM Gord Dibben wrote: I'm betting you have some "merged cells" which won't Autofit without VBA code. I am betting you have some "merged cells" which will not Autofit without VBA code. If this is the case and you are interested in the code, post back. Gord Dibben MS Excel MVP wrote: On Thursday, February 22, 2007 2:38 PM Caro wrote: Nevermind! Nevermind! Just realized my cells are merged - therefore, none of the solutions I was trying will work. -- Carol "Carol" wrote: On Thursday, February 22, 2007 2:42 PM Caro wrote: It is the case! It is the case! I think we were both responding to my post at the same time.. I would love the VBA code - though a novice at that. HOWEVER - if it's easier than completely re-doing my form, I'll be happy to take a stab at it! Thank you Gord! -- Carol "Gord Dibben" wrote: On Thursday, February 22, 2007 3:05 PM Gord Dibben wrote: I avoid meged cells like the plague simply due to the many problems caused I avoid meged cells like the plague simply due to the many problems caused by these. But............................ Here is event code from Greg Wilson. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText 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 With End Sub This is sheet event code. Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. As you enter data the rows will autofit. Gord On Thu, 22 Feb 2007 11:42:45 -0800, Carol wrote: On Thursday, February 22, 2007 4:50 PM Caro wrote: You (and Greg Wilson)...... BRILLIANT! Worked beautifully! You (and Greg Wilson)...... BRILLIANT! Worked beautifully! Thank you for help - and perfect instruction! -- Carol "Gord Dibben" wrote: On Wednesday, October 15, 2008 7:35 PM Bria wrote: Does anyone know how to modify this code so that it will work in a protected Does anyone know how to modify this code so that it will work in a protected sheet? "Gord Dibben" wrote: On Wednesday, October 15, 2008 10:33 PM Gord Dibben wrote: Private Sub Worksheet_Change(ByVal Target As Range)Dim NewRwHt As SingleDim Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range On Error GoTo endit Application.EnableEvents = False With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 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 With endit: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord On Wed, 15 Oct 2008 16:35:03 -0700, Brian wrote: On Thursday, October 16, 2008 9:31 AM Bria wrote: Thank you, Gord! Thank you, Gord! Note that I did have a slight issue where the resized cells were becoming locked after editing, but I stole a line of code (ma.Locked = False) from another post, and that seems to solve the issue. Thanks again. "Gord Dibben" wrote: On Thursday, October 16, 2008 12:37 PM Gord Dibben wrote: Autosize cells as data is entered Good to hear Thanks for the feedback wrote: On Monday, June 08, 2009 1:45 PM brianna wrote: will this work for data that has already been entered into my sheet or will will this work for data that has already been entered into my sheet or will this only work on a new sheet and any data entered after it is applied? "Gord Dibben" wrote: On Monday, June 08, 2009 11:59 PM Gord Dibben wrote: Only works when entering new data or editing existing data. Only works when entering new data or editing existing data. Gord On Thursday, March 18, 2010 11:50 AM donna-LexusWebs wrote: I added the VBA to an existing worksheet and although it did not expand myrows I added the VBA to an existing worksheet and although it did not expand my rows as soon as I added the code, as soon as I typed into the cell again the row expanded and stayed expanded. I hope that helps! Thanks for the code - and the specific instructions where to add it (rt-clk on the tab). I never knew that and always wondered how code was being added!! Thanks again! "Gord Dibben" wrote: Submitted via EggHeadCafe - Software Developer Portal of Choice WPF Report Engine, Part 1 http://www.eggheadcafe.com/tutorials...ne-part-1.aspx |
| All times are GMT +1. The time now is 12:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com