Named Ranges - Macro Problems

[Karl Burrows]

Hi!

I created several worksheets that contained huge lists, so I created a
dynamic named range. Now that the name has been inserted in my array
formulas, my Macro runs very slowly. The Macro unprotects one of my sheets,
sorts the data in a table, hides the blank rows and the protects the sheet
again. There are no related named range formulas in that worksheet, so I'm
not sure why it would be affected. The Macro ran quickly when I had a
defined range of data.

Would giving a named range like 'Date' and having text in my workbook with
'Date' create conflicts? Could this affect my Macros?

I'll be more than happy to send the formulas and Macro if you want to look
at them further.

Thanks!

 

[Charles Williams]

Hi Karl,

Check that you have

Application.calculation=xlManual

.... your code

Application.calculation=xlautomatic


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Karl Burrows]

I am going to attach the code. I put this together from several smaller
snippets of code (I am not a VBA expert by any stretch of the imagination).
Are there any other suggestions to clean up my code to help it run smoother?

Thanks!

Sub UnhideSortHideRows()
ActiveSheet.Unprotect Password:="xxxx"
'
' UnhideRows Macro
'
Rows("11:312").Select
Selection.EntireRow.Hidden = False

' SortSummary Macro
'
Range("B10:K106").Select
Selection.Sort Key1:=Range("B11"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B113:K209").Select
Selection.Sort Key1:=Range("B114"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B216:K312").Select
Selection.Sort Key1:=Range("B217"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

' HideBlankRows Macro
'
'
Const MYCOL As String = "B"
Const STARTROW As Long = 11
Dim rng As Range

Application.ScreenUpdating = False
With ActiveSheet
.Rows(STARTROW).Insert
.UsedRange
With Range(.Cells(STARTROW, MYCOL), .Cells(Rows.Count, _
MYCOL).End(xlUp).Offset(-1, 0))
.AutoFilter Field:=1, Criteria1:="=", _
Operator:=xlOr, Criteria2:="=0"
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter Field:=1
End With
End With
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
ActiveSheet.Rows(STARTROW).Delete
Application.ScreenUpdating = True
Range("A1").Select
ActiveSheet.Protect Password:="xxxx"
End Sub