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]

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

 

[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?

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 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

 

[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.

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

 

[drdavidge]

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

 

[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]

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

 

[drdavidge]

think i found it...

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

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