macro takes 30+ seconds to do an autofit on only 1 out of the 3 sheets it is autofitting

[drdavidge]

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

[Peter T]

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

Regards,
Peter T

"drdavidge" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[drdavidge]

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?

[Peter T]

"drdavidge" <[email protected]> wrote
in message news:[email protected]...
>
> 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

[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

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:

Please Login or Register  to view this content.

thanks again.

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[drdavidge]

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?

[Peter T]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[drdavidge]

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

interesting. what is the equivilant of ctrl-shift-end in VBA?

[drdavidge]

think i found it...

Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select

looks like it working a lot faster now... thanks for your help!