First, I'd stay away from using VBA terms as my variable (Rows)

And when you're working with strings, it's better to use "&" to concatenate
(instead of "+").

This worked ok for me:

Option Explicit

Private Sub Adjust_Rows_Click()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("D65536").End(xlUp).Row
.Range("D4:D" & LastRow).EntireRow.AutoFit
End With
End Sub

(I didn't need the cstr() function. VBA is forgiving of some things.)



Wellie wrote:

The following are the simple few lines of code to adjust
the height of rows when user clicks the "Adjust Rows"
button. I keep on getting a run-time error "1004" saying
AutFit method of Range class failed.

Can someone please tell me where it fails ?

PS: The column contains data need adjust is Column D with
with more than 999 rows of data.

Private sub Adjust_Rows_Click()
Dim Rows as Integer

Rows = ActiveSheet.Range("D999").End(xlUp).Row
ActiveSheet.Range("D4:D"+Cstr(Rows)).Rows.AutoFit
End Sub

Thanks in advance for any help.

--

Dave Peterson