| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofit row code
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
autofit row code
Nick,
Can I do... ..Rows("1:3").AutoFit Thanks! --Randy "Nick Hodge" wrote in message ... 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 HIS "Randy Starkey" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
autofit row code
certainly
-- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Randy Starkey" wrote in message ... Nick, Can I do... .Rows("1:3").AutoFit Thanks! --Randy "Nick Hodge" wrote in message ... 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 HIS "Randy Starkey" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
autofit row code
Nick,
I wonder if I had this backwards... myRng.AutoFit.Row should it simply be... myRng.Row.AutoFit ? Thanks! --Randy "Nick Hodge" wrote in message ... certainly -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Randy Starkey" wrote in message ... Nick, Can I do... .Rows("1:3").AutoFit Thanks! --Randy "Nick Hodge" wrote in message ... 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 HIS "Randy Starkey" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
autofit row code
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 HIS "Randy Starkey" wrote in message ... Nick, I wonder if I had this backwards... myRng.AutoFit.Row should it simply be... myRng.Row.AutoFit ? Thanks! --Randy "Nick Hodge" wrote in message ... certainly -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Randy Starkey" wrote in message ... Nick, Can I do... .Rows("1:3").AutoFit Thanks! --Randy "Nick Hodge" wrote in message ... 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 HIS "Randy Starkey" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
autofit row code
Nick,
Ah! Yes. The thing I liked about using the myRng is it leaves off rows 1-3, correct? --Randy "Nick Hodge" wrote in message ... 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 HIS "Randy Starkey" wrote in message ... Nick, I wonder if I had this backwards... myRng.AutoFit.Row should it simply be... myRng.Row.AutoFit ? Thanks! --Randy "Nick Hodge" wrote in message ... certainly -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Randy Starkey" wrote in message ... Nick, Can I do... .Rows("1:3").AutoFit Thanks! --Randy "Nick Hodge" wrote in message ... 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 HIS "Randy Starkey" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
autofit row code
Correct
-- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Randy Starkey" wrote in message ... Nick, Ah! Yes. The thing I liked about using the myRng is it leaves off rows 1-3, correct? --Randy "Nick Hodge" wrote in message ... 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 HIS "Randy Starkey" wrote in message ... Nick, I wonder if I had this backwards... myRng.AutoFit.Row should it simply be... myRng.Row.AutoFit ? Thanks! --Randy "Nick Hodge" wrote in message ... certainly -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Randy Starkey" wrote in message ... Nick, Can I do... .Rows("1:3").AutoFit Thanks! --Randy "Nick Hodge" wrote in message ... 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 HIS "Randy Starkey" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Autofit (Columns.EntireColumn.AutoFit) does not work | Excel Discussion (Misc queries) | |||
| Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
| Need code to Autofit Row Height on recalculation | Excel Discussion (Misc queries) | |||
| Code for autofit/merged cells doesn't work | Excel Programming | |||
| Merged Cells Autofit - code amendment | Excel Programming | |||
Linear Mode
