Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Row Height
Hi all,
I have a table with 3 columns - C,D and M. D (D to L) and M (M to U) are merged cells. Column d contains formulas linked to another sheet. this column is locked. when i paste information into a cell in column D, i would like the entire row to be automatically resized to fit the information. The columns are already text wrapped. This would preferably happen when i Tab to another cell. Oh, and Column M is locked! At the moment the row just shows what fits and the rest disappears and is only visible in the formula bar. this is obviously a problem when i want to print! I would appreciate any help on this, Thanks in advance, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Row Height
I see that you have had no responses yet.
Here is code that I have written, to do the full worksheet, you can trim it down to just work on the row in question. To get what you are basically wanting, if you add code to the Worksheet in question that uses the built in Change sub routine, you can call the resize as necessary. In my creation of this, I did not find a way to determine if all of the data in the cell is actually showing or not, so you may still run into the problem you are experiencing. Remember wrapping text does not resize the cell when if the cell data is changed after implementing the wrap. Really looking at this, you just need to call Rows(Target.Row).EntireRow.AutoFit whenever you change data in the row... Now if you want to resize only while making changes within a certain group or particular row, you can place that command (Rows(Target.Row).EntireRow.AutoFit) within an if or nested if statement, or a Select Case statement. Something like that.. Option Explicit Private Sub RowHeight() ' ' Macro1 Macro ' Macro recorded 07/23/2003 ' ' Dim i As Integer Dim MaxRows As Integer MaxRows = 1 While Cells(MaxRows, 1).Text < "" MaxRows = MaxRows + 1 Wend For i = 1 To MaxRows - 1 Rows(i).EntireRow.AutoFit Next i 'Rows("519:519").EntireRow.AutoFit End Sub Private Sub ColumnWidth() Dim i As Integer Dim MaxCols As Integer MaxCols = 256 'While Cells(MaxRows, 1).Text < "" ' MaxRows = MaxRows + 1 'Wend For i = 1 To MaxCols Columns(i).EntireColumn.AutoFit Next i 'Rows("519:519").EntireRow.AutoFit End Sub Public Sub RowFirst() Application.ScreenUpdating = False Call RowHeight Call ColumnWidth Application.ScreenUpdating = True End Sub Public Sub ColFirst() Application.ScreenUpdating = False Call ColumnWidth Call RowHeight Application.ScreenUpdating = True End Sub Public Sub ColOnly() Application.ScreenUpdating = False Call ColumnWidth Application.ScreenUpdating = True End Sub Public Sub RowOnly() Application.ScreenUpdating = False Call RowHeight Application.ScreenUpdating = True End Sub "RemySS" wrote: Hi all, I have a table with 3 columns - C,D and M. D (D to L) and M (M to U) are merged cells. Column d contains formulas linked to another sheet. this column is locked. when i paste information into a cell in column D, i would like the entire row to be automatically resized to fit the information. The columns are already text wrapped. This would preferably happen when i Tab to another cell. Oh, and Column M is locked! At the moment the row just shows what fits and the rest disappears and is only visible in the formula bar. this is obviously a problem when i want to print! I would appreciate any help on this, Thanks in advance, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto height | Excel Worksheet Functions | |||
Auto fit row height | Excel Worksheet Functions | |||
Auto Fit on Row Height | Excel Discussion (Misc queries) | |||
AUTO ROW HEIGHT | Excel Worksheet Functions | |||
Row Height - Auto-Fit | Excel Discussion (Misc queries) |