autofit row code

Randy Starkey · 03-23-2005, 04:06 AM

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 · 03-23-2005, 04:06 AM

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
[email protected]HIS

"Randy Starkey" <[email protected]> wrote in
message news:[email protected]...
>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 Starkey · 03-23-2005, 02:06 PM

Nick,

Can I do...

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

Thanks!

--Randy

"Nick Hodge" <[email protected]> wrote in message
news:%[email protected]...
> 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
> [email protected]HIS
>
>
> "Randy Starkey" <[email protected]> wrote in
> message news:[email protected]...
>>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 · 03-24-2005, 04:06 AM

certainly

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS

"Randy Starkey" <[email protected]> wrote in
message news:[email protected]...
> Nick,
>
> Can I do...
>
> .Rows("1:3").AutoFit
>
> Thanks!
>
> --Randy
>
>
> "Nick Hodge" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>> [email protected]HIS
>>
>>
>> "Randy Starkey" <[email protected]> wrote in
>> message news:[email protected]...
>>>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 Starkey · 03-24-2005, 04:06 PM

Nick,

I wonder if I had this backwards...

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

Thanks!

--Randy

"Nick Hodge" <[email protected]> wrote in message
news:[email protected]...
> certainly
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> [email protected]HIS
>
>
> "Randy Starkey" <[email protected]> wrote in
> message news:[email protected]...
>> Nick,
>>
>> Can I do...
>>
>> .Rows("1:3").AutoFit
>>
>> Thanks!
>>
>> --Randy
>>
>>
>> "Nick Hodge" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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
>>> [email protected]HIS
>>>
>>>
>>> "Randy Starkey" <[email protected]> wrote in
>>> message news:[email protected]...
>>>>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 · 03-24-2005, 09:06 PM

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
[email protected]HIS

"Randy Starkey" <[email protected]> wrote in
message news:[email protected]...
> Nick,
>
> I wonder if I had this backwards...
>
> myRng.AutoFit.Row
> should it simply be...
> myRng.Row.AutoFit ?
>
> Thanks!
>
> --Randy
>
>
> "Nick Hodge" <[email protected]> wrote in message
> news:[email protected]...
>> certainly
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> [email protected]HIS
>>
>>
>> "Randy Starkey" <[email protected]> wrote in
>> message news:[email protected]...
>>> Nick,
>>>
>>> Can I do...
>>>
>>> .Rows("1:3").AutoFit
>>>
>>> Thanks!
>>>
>>> --Randy
>>>
>>>
>>> "Nick Hodge" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>> 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
>>>> [email protected]HIS
>>>>
>>>>
>>>> "Randy Starkey" <[email protected]> wrote in
>>>> message news:[email protected]...
>>>>>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 Starkey · 03-24-2005, 10:06 PM

Nick,

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

--Randy

"Nick Hodge" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]HIS
>
>
> "Randy Starkey" <[email protected]> wrote in
> message news:[email protected]...
>> Nick,
>>
>> I wonder if I had this backwards...
>>
>> myRng.AutoFit.Row
>> should it simply be...
>> myRng.Row.AutoFit ?
>>
>> Thanks!
>>
>> --Randy
>>
>>
>> "Nick Hodge" <[email protected]> wrote in message
>> news:[email protected]...
>>> certainly
>>>
>>> --
>>> HTH
>>> Nick Hodge
>>> Microsoft MVP - Excel
>>> Southampton, England
>>> [email protected]HIS
>>>
>>>
>>> "Randy Starkey" <[email protected]> wrote in
>>> message news:[email protected]...
>>>> Nick,
>>>>
>>>> Can I do...
>>>>
>>>> .Rows("1:3").AutoFit
>>>>
>>>> Thanks!
>>>>
>>>> --Randy
>>>>
>>>>
>>>> "Nick Hodge" <[email protected]> wrote in message
>>>> news:%[email protected]...
>>>>> 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
>>>>> [email protected]HIS
>>>>>
>>>>>
>>>>> "Randy Starkey" <[email protected]> wrote in
>>>>> message news:[email protected]...
>>>>>>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 · 03-25-2005, 08:06 AM

Correct

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS

"Randy Starkey" <[email protected]> wrote in
message news:[email protected]...
> Nick,
>
> Ah! Yes. The thing I liked about using the myRng is it leaves off rows
> 1-3, correct?
>
> --Randy
>
>
> "Nick Hodge" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> [email protected]HIS
>>
>>
>> "Randy Starkey" <[email protected]> wrote in
>> message news:[email protected]...
>>> Nick,
>>>
>>> I wonder if I had this backwards...
>>>
>>> myRng.AutoFit.Row
>>> should it simply be...
>>> myRng.Row.AutoFit ?
>>>
>>> Thanks!
>>>
>>> --Randy
>>>
>>>
>>> "Nick Hodge" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> certainly
>>>>
>>>> --
>>>> HTH
>>>> Nick Hodge
>>>> Microsoft MVP - Excel
>>>> Southampton, England
>>>> [email protected]HIS
>>>>
>>>>
>>>> "Randy Starkey" <[email protected]> wrote in
>>>> message news:[email protected]...
>>>>> Nick,
>>>>>
>>>>> Can I do...
>>>>>
>>>>> .Rows("1:3").AutoFit
>>>>>
>>>>> Thanks!
>>>>>
>>>>> --Randy
>>>>>
>>>>>
>>>>> "Nick Hodge" <[email protected]> wrote in
>>>>> message news:%[email protected]...
>>>>>> 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
>>>>>> [email protected]HIS
>>>>>>
>>>>>>
>>>>>> "Randy Starkey" <[email protected]> wrote
>>>>>> in message news:[email protected]...
>>>>>>>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
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>