AutoFit method of Range class failed

  • Thread starter Thread starter Wellie
  • Start date Start date
W

Wellie

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.
 
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.)
 
Back
Top