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

You've probably got a very large Usedrange, try Ctrl-End.

Regards,
Peter T

"drdavidge" <drdavidge@gmail.com> wrote in message
news:1152802533.011692.99170@m79g2000cwm.googlegroups.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
>

Originally Posted by Peter T

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" <drdavidge.2aw4t6_1152805511.4042@excelforum-nospam.com> wrote
in message news:drdavidge.2aw4t6_1152805511.4042@excelforum-nospam.com...
>
> 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

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" <drdavidge.2aw7cs_1152808808.9322@excelforum-nospam.com> wrote
in message news:drdavidge.2aw7cs_1152808808.9322@excelforum-nospam.com...
>
> 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
>