Clean Up without reducing print window.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Currently I have a macro that searches column A row 9+ for a numeric value.
If no value is found in column A of that row, the row is deleted and all
other rows move up. The macro does this function just fine. The problem is,
it shrinks the print window when it deletes all these rows. Is there a way
to edit this macro so that it still deletes all the rows with nothing in
column A, still moves all rows with a value in column A up BUT does not
shrink the print window?

Code I currently use:

Sub CleanUp()

Dim wS1 As Work***
Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

Set wS1 = Workbooks("Order Form.xls").Sheets("Order Form")

wS1.Unprotect Password:="xxxxx"

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

DeleteValue = ""

With Active***

.AutoFilterMode = False

.Range("A9:A" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

.AutoFilterMode = False
End With

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

wS1.Protect Password:="xxxxx"
Active***.Range("A10").Select

End Sub

Thank you!
.

Quantcast