autofit row code

  • Thread starter Thread starter Randy Starkey
  • Start date Start date
R

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
 
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)
 
certainly

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

I wonder if I had this backwards...

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

Thanks!

--Randy
 
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)
 
Nick,

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

--Randy
 
Correct

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