CODEWITHSUNDEEP
excelvba
Kaylynn Enright
Kaylynn Enright

Reputation: 27

How to delete everything outside of the print area in Excel?

I am new to VBA and am trying to delete everything outside of the specified print area for every worksheet in my file. I have code that is working alright, but for some tabs, the print area begins in column B, and I need to delete column A because it is not in the print area. I cannot figure out how to rewrite my code to ensure that the column to the left of the specified print area will get deleted.

Dim FirstEmptyRow As Long
Dim FirstEmptyCol As Integer
Dim rng As Range
With ActiveSheet.PageSetup
        If .PrintArea = "" Then
            Set rng = ActiveSheet.UsedRange
        Else
            Set rng = ActiveSheet.Range(.PrintArea)
        End If
    End With

    FirstEmptyCol = rng.Cells(rng.Cells.Count).Column + 1
    FirstEmptyRow = rng.Rows.Count + rng.Cells(1).Row

    Range(Cells(1, FirstEmptyCol), Cells(1, 256)).EntireColumn.Delete
    Range(Cells(FirstEmptyRow, 1), Cells(Rows.Count, 1)).EntireRow.Delete

Upvotes: 0

Views: 2419

Answers (3)

chris neilsen
chris neilsen

Reputation: 53125

You can use the Column and Row properties of a range to determine where it starts, like this

Sub DeleteOutsidePrintArea(ws As Worksheet)
    Dim rng As Range

    With ws
        If .PageSetup.PrintArea = vbNullString Then
            Set rng = .UsedRange
        Else
            Set rng = .Range(.PageSetup.PrintArea)
        End If

        ' Delete columns to left, if any
        If rng.Column > 1 Then
            .Columns(1).Resize(, rng.Column - 1).Delete
        End If

        ' Delete rows above, if any
        If rng.Row > 1 Then
            .Rows(1).Resize(rng.Row - 1).Delete
        End If

        ' Delete columns to right, if any
        If rng.Columns.Count < (.UsedRange.Columns.Count + .UsedRange.Column - 1) Then
            .Columns(rng.Columns.Count + 1).Resize(, .UsedRange.Columns.Count + .UsedRange.Column - 1 - rng.Columns.Count).Delete
        End If

        ' Delete rows below, if any
        If rng.Rows.Count < (.UsedRange.Rows.Count + .UsedRange.Row - 1) Then
            .Rows(rng.Rows.Count + 1).Resize(.UsedRange.Rows.Count + .UsedRange.Row - 1 - rng.Rows.Count).Delete
        End If
    End With
End Sub

Call it like this

Sub Demo()
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook '<~~~ Adjust to suit
    For Each ws In wb.Worksheets
        DeleteOutsidePrintArea ws
    Next
End Sub

Upvotes: 1

Miles Fett
Miles Fett

Reputation: 721

You could try this. Find the PrintAreaand then using Intersect you could loop through the cells and find which cells are not in the PrintArea, Union the cells and then delete them at the end. Doing it this way you can delete everything that's not a part of the PrintArea, all at the same time. Hope this helps:

Sub testPrintArea()
    Dim printAreaRange As Range
        With ActiveSheet.PageSetup
        If .PrintArea = "" Then
            Set printAreaRange = ActiveSheet.UsedRange
        Else
            Set printAreaRange = ActiveSheet.Range(.PrintArea)
        End If
    End With

    ' Get non print area cells and union them
    Dim nonPrintAreaCells As Range
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange
        If Intersect(cell, printAreaRange) Is Nothing Then
            If nonPrintAreaCells Is Nothing Then
                Set nonPrintAreaCells = cell
            Else
                Set nonPrintAreaCells = Union(nonPrintAreaCells, cell)
            End If
        End If
    Next cell

    ' do whatever...
    nonPrintAreaCells.Value = ""
End Sub

Upvotes: 1

tlemaster
tlemaster

Reputation: 859

Try adding this additional code:

 If rng.Column > 1 Then
    Range(Cells(1, 1), Cells(1, rng.Column - 1)).EntireColumn.Delete
 End If

Upvotes: 1

Related Questions