ExcelBanter - 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,