Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro takes 30+ seconds to do an autofit on only 1 out of the 3 sheets it is autofitting
hey, for some reason this macro is taking 30+ seconds to perform a font
change and autofit on the 2nd sheet in this workbook. sheets 1 and 3 are almost identical (in rows and columns) and those happen in about 1 second or less. i am not sure why the 2nd sheet (ReArranged - No Formulas) is taking so much longer then any of the other ones. i was able to determine that the 2nd sheet was taking longer by using breakpoints in the debugging. any ideas why? the code is below: 'Change fonts and fix column widths Dim sheetArray(3) sheetArray(1) = "ReArranged" sheetArray(2) = "ReArranged - No Formulas" sheetArray(3) = "DO NOT USE" For L = 1 To 3 Sheets(sheetArray(L)).Select Cells.Select With Selection.Font .Name = "MS Sans Serif" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With Cells.Select Selection.Columns.AutoFit Range("A1").Select If sheetArray(L) = "DO NOT USE" Then Columns("A:A").ColumnWidth = 6.5 End If Next L |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro takes 30+ seconds to do an autofit on only 1 out of the 3 sheets it is autofitting
You've probably got a very large Usedrange, try Ctrl-End.
Regards, Peter T "drdavidge" wrote in message ups.com... hey, for some reason this macro is taking 30+ seconds to perform a font change and autofit on the 2nd sheet in this workbook. sheets 1 and 3 are almost identical (in rows and columns) and those happen in about 1 second or less. i am not sure why the 2nd sheet (ReArranged - No Formulas) is taking so much longer then any of the other ones. i was able to determine that the 2nd sheet was taking longer by using breakpoints in the debugging. any ideas why? the code is below: 'Change fonts and fix column widths Dim sheetArray(3) sheetArray(1) = "ReArranged" sheetArray(2) = "ReArranged - No Formulas" sheetArray(3) = "DO NOT USE" For L = 1 To 3 Sheets(sheetArray(L)).Select Cells.Select With Selection.Font .Name = "MS Sans Serif" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With Cells.Select Selection.Columns.AutoFit Range("A1").Select If sheetArray(L) = "DO NOT USE" Then Columns("A:A").ColumnWidth = 6.5 End If Next L |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro takes 30+ seconds to do an autofit on only 1 out of the 3 sheets it is autofitting
Peter T Wrote: You've probably got a very large Usedrange, try Ctrl-End. Regards, Peter T With Ctrl-End, it goes from A1 to BR568 - which is the same as th first sheet. That is all the data (kind of a lot). Any other ideas -- drdavidg ----------------------------------------------------------------------- drdavidge's Profile: http://www.excelforum.com/member.php...fo&userid=3616 View this thread: http://www.excelforum.com/showthread.php?threadid=56110 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro takes 30+ seconds to do an autofit on only 1 out of the 3 sheets it is autofitting
"drdavidge" wrote
in message ... Peter T Wrote: You've probably got a very large Usedrange, try Ctrl-End. Regards, Peter T With Ctrl-End, it goes from A1 to BR568 - which is the same as the first sheet. That is all the data (kind of a lot). Any other ideas? -- drdavidge That's not a large Usedrange, guess there's something else lurking on that sheet. Try deleting all columns to the right and rows below BR568 If that doesn't make a difference, with a *backup* - Insert a new sheet one to the left - Cut A1:BR568 and paste into the new sheet - delete the now empty sheet - rename the new same as old Regards, Peter T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro takes 30+ seconds to do an autofit on only 1 out of the 3 sheets it is autofitting
Peter T Wrote: That's not a large Usedrange, guess there's something else lurking o that sheet. Try deleting all columns to the right and rows below BR568 If that doesn't make a difference, with a *backup* - Insert a new sheet one to the left - Cut A1:BR568 and paste into the new sheet - delete the now empty sheet - rename the new same as old Regards, Peter T hmm.. the "ReArranged - No Formulas" sheet gets generated every time run the macro by copying the entire sheet and pasting special value from the original "ReArranged" sheet. On second look, it does seem lik the new sheet is 64k rows long and IV colums wide. it seems like i hav two options at this point: 1) is there a way i can delete those extra rows/columns in "ReArrange - No Formulas" with VBA? or 2) would it be better to just select the data cells in "ReArranged (A1:BR568) when i originally copy it? if so. how can i do that so tha it works when there is a different number of rows/colums? (it won always be A1:BR568) ? currently i do it like this: Code ------------------- 'Create new "No Formula" Sheet Sheets("ReArranged - No Formulas").Select Sheets("ReArranged").Cells.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ------------------- thanks again -- drdavidg ----------------------------------------------------------------------- drdavidge's Profile: http://www.excelforum.com/member.php...fo&userid=3616 View this thread: http://www.excelforum.com/showthread.php?threadid=56110 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro takes 30+ seconds to do an autofit on only 1 out of the 3 sheets it is autofitting
Did you try manually and CUT the old and paste all including formulas to the
new sheet. But now it seems your UR is enormous, is that the old or new sheet. Did you delete rows & columns to right & below your last 'data' cell. Regards, Peter T PS, did you at any stage have hidden rows & columns. "drdavidge" wrote in message ... Peter T Wrote: That's not a large Usedrange, guess there's something else lurking on that sheet. Try deleting all columns to the right and rows below BR568 If that doesn't make a difference, with a *backup* - Insert a new sheet one to the left - Cut A1:BR568 and paste into the new sheet - delete the now empty sheet - rename the new same as old Regards, Peter T hmm.. the "ReArranged - No Formulas" sheet gets generated every time i run the macro by copying the entire sheet and pasting special values from the original "ReArranged" sheet. On second look, it does seem like the new sheet is 64k rows long and IV colums wide. it seems like i have two options at this point: 1) is there a way i can delete those extra rows/columns in "ReArranged - No Formulas" with VBA? or 2) would it be better to just select the data cells in "ReArranged" (A1:BR568) when i originally copy it? if so. how can i do that so that it works when there is a different number of rows/colums? (it wont always be A1:BR568) ? currently i do it like this: Code: -------------------- 'Create new "No Formula" Sheet Sheets("ReArranged - No Formulas").Select Sheets("ReArranged").Cells.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False -------------------- thanks again. -- drdavidge ------------------------------------------------------------------------ drdavidge's Profile: http://www.excelforum.com/member.php...o&userid=36168 View this thread: http://www.excelforum.com/showthread...hreadid=561106 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro takes 30+ seconds to do an autofit on only 1 out of the 3 sheets it is autofitting
Peter T Wrote: Did you try manually and CUT the old and paste all including formulas t the new sheet. But now it seems your UR is enormous, is that the old or new sheet. Di you delete rows & columns to right & below your last 'data' cell. Regards, Peter T PS, did you at any stage have hidden rows & columns. The old sheet's ctrl-end range is the a1:br568 so that sheet is fine. guess when i do the Cells.Select it selects every single cell beyon that range? is there a way to select all data filled rows/column instead -- drdavidg ----------------------------------------------------------------------- drdavidge's Profile: http://www.excelforum.com/member.php...fo&userid=3616 View this thread: http://www.excelforum.com/showthread.php?threadid=56110 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro takes 30+ seconds to do an autofit on only 1 out of the 3 sheets it is autofitting
The whole point was NOT to copy or cut the whole sheet, otherwise will
transfer the same problem. However the fact you have and now find the UR on the newly pasted sheet is very large suggests something was wrong on the original sheet. Select A1 Ctrl-Shift-End ' should select A1:BR568 Cut Regards, Peter T "drdavidge" wrote in message ... Peter T Wrote: Did you try manually and CUT the old and paste all including formulas to the new sheet. But now it seems your UR is enormous, is that the old or new sheet. Did you delete rows & columns to right & below your last 'data' cell. Regards, Peter T PS, did you at any stage have hidden rows & columns. The old sheet's ctrl-end range is the a1:br568 so that sheet is fine. i guess when i do the Cells.Select it selects every single cell beyond that range? is there a way to select all data filled rows/columns instead? -- drdavidge ------------------------------------------------------------------------ drdavidge's Profile: http://www.excelforum.com/member.php...o&userid=36168 View this thread: http://www.excelforum.com/showthread...hreadid=561106 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro takes 30+ seconds to do an autofit on only 1 out of the 3 sheets it is autofitting
Peter T Wrote: The whole point was NOT to copy or cut the whole sheet, otherwise will transfer the same problem. However the fact you have and now find th UR on the newly pasted sheet is very large suggests something was wrong o the original sheet. Select A1 Ctrl-Shift-End ' should select A1:BR568 Cut Regards, Peter T interesting. what is the equivilant of ctrl-shift-end in VBA -- drdavidg ----------------------------------------------------------------------- drdavidge's Profile: http://www.excelforum.com/member.php...fo&userid=3616 View this thread: http://www.excelforum.com/showthread.php?threadid=56110 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro takes 30+ seconds to do an autofit on only 1 out of the 3 sheets it is autofitting
think i found it... Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select looks like it working a lot faster now... thanks for your help -- drdavidg ----------------------------------------------------------------------- drdavidge's Profile: http://www.excelforum.com/member.php...fo&userid=3616 View this thread: http://www.excelforum.com/showthread.php?threadid=56110 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Takes 30 Seconds Plus to Save First Time | Excel Discussion (Misc queries) | |||
Saving takes 30 Seconds | Excel Discussion (Misc queries) | |||
Copying single cell takes 15-20 seconds | Excel Discussion (Misc queries) | |||
macro takes 30+ seconds to do an autofit on only 1 of 3 sheets it is autofitting | Excel Worksheet Functions | |||
Save takes seconds vs. SaveAs/Save As takes minutes | Excel Programming |