I have this code below, I found from a previous post in Ozgrid, I modified a little bit but cant seem to get it to do what I need. I want to go through each worksheet in my workbook and delete what is outside the print range. The print range varies for each sheet. This code is not looping through each sheet. Any suggestions?
Code
Sub ClearPrintRange()
'http://www.ozgrid.com/forum/archive/index.php/t-37666.html
For Each ws In Worksheets
Dim PrintRange As Range
Dim Range_Top As Integer
Dim Range_Left As Integer
Dim Range_Bottom As Integer
Dim Range_Right As Integer
Set PrintRange = Range("Print_Area")
Range_Top = PrintRange.Row
Range_Left = PrintRange.Column
Range_Bottom = PrintRange.Rows(PrintRange.Rows.Count).Row
Range_Right = PrintRange.Columns(PrintRange.Columns.Count).Column
'delete from the bottom row down first.
If Range_Bottom < 65535 Then
ActiveSheet.Range(Range_Bottom + 1 & ":65536").Select
Selection.Delete
End If
'delete from the top row up.
If Range_Top > 1 Then
ActiveSheet.Range("1:" & Range_Top - 1).Select
Selection.Delete
End If
'delete from the right hand side next.
If Range_Right < 255 Then
ActiveSheet.Range(ActiveSheet.Columns(Range_Right + 1), ActiveSheet.Columns(256)).Select
Selection.Delete
End If
'lastly delete from the left hand side.
If Range_Left > 1 Then
ActiveSheet.Range(ActiveSheet.Columns(Range_Left - 1), ActiveSheet.Columns(1)).Select
Selection.Delete
End If
Next ws
End Sub
Display More