autofit row code

[Randy Starkey]

I would like to look at all rows in an active sheet (from row 3 down) and
then autofit the rows. I'd like to add this to my sort macro.

Here's what I've got...

Sub Hospital_Sheet_Sort()
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("A3:V" & .Cells(.Rows.Count, "A").End(xlUp).Row)
myRng.Sort Key1:=Range("A3"), Order1:=xlAscending, Key2:=Range("N3") _
, Order2:=xlDescending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal
End With
myRng.AutoFit.Row
Range("A3:A3").Select
End Sub

I get an error on the myRng autofitting.
I'm doing something wrong obviously...
Any help would be appreciated.

Thanks!

--Randy Starkey

 

[Nick Hodge]

Randy

I suppose it doesn't matter if you autofit *all* rows (inc 1-3?)

You could then simply put above the End With statement

..Rows.AutoFit

If you then need to set back the top three you could do that with

..Rows("1:3").RowHeight = 12

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Randy Starkey]

Nick,

Can I do...

..Rows("1:3").AutoFit

Thanks!

--Randy

 

[Nick Hodge]

certainly

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Randy Starkey]

Nick,

I wonder if I had this backwards...

myRng.AutoFit.Row
should it simply be...
myRng.Row.AutoFit ?

Thanks!

--Randy

 

[Nick Hodge]

Randy

Yes...the second is the correct syntax, but you need rowS

..Rows.Autofit

Note: In the 'With.... End With' statement, you are already utilising the
ActiveSheet, so you don't need the range variable at this time because the
statement refers to all the rows on the activesheet this way.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Randy Starkey]

Nick,

Ah! Yes. The thing I liked about using the myRng is it leaves off rows 1-3,
correct?

--Randy

 

[Nick Hodge]

Correct

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)