Clean Up without reducing print window.
 

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

.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"
ActiveSheet.Range("A10").Select

End Sub

Thank you!

Clean Up without reducing print window.
 

EDIT:

I think I'll keep this as it is. I do however have another Macro that I
use to restore the deleted rows. Is there a simple function/code I can enter
into this Macro to restore the Print Area to my desired range? For example,
my Clean Up macro reduces the Print Area to A1:G34. Then I run my Restore
macro and want the Print Area to change to A1:G109.

Thank you!

Clean Up without reducing print window.
 

Don't set the print area until just before the pring command. That way, it
is not subject to change.

ActiveSheet.PageSetup.PrintArea = Range("A1:G109")
ActiveSheet.PrintOut

"Ryan Hess" wrote:

EDIT:

I think I'll keep this as it is. I do however have another Macro that I
use to restore the deleted rows. Is there a simple function/code I can enter
into this Macro to restore the Print Area to my desired range? For example,
my Clean Up macro reduces the Print Area to A1:G34. Then I run my Restore
macro and want the Print Area to change to A1:G109.

Thank you!

Clean Up without reducing print window.
 

That works great JLGWhiz!

Thanks again!


"JLGWhiz" wrote:

Don't set the print area until just before the pring command. That way, it
is not subject to change.

ActiveSheet.PageSetup.PrintArea = Range("A1:G109")
ActiveSheet.PrintOut

"Ryan Hess" wrote:

EDIT:

I think I'll keep this as it is. I do however have another Macro that I
use to restore the deleted rows. Is there a simple function/code I can enter
into this Macro to restore the Print Area to my desired range? For example,
my Clean Up macro reduces the Print Area to A1:G34. Then I run my Restore
macro and want the Print Area to change to A1:G109.

Thank you!