如何在Excel中删除打印区域之外的所有内容？

Question

我是VBA新手，正在尝试为文件中的每个工作表删除指定打印区域之外的所有内容。我的代码运行正常，但对于某些选项卡，打印区域从B列开始，我需要删除A列，因为它不在打印区域中。我不知道如何重写代码以确保指定打印区域左侧的列将被删除。

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

Answer 1

尝试添加此附加代码：

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

Answer 2

你可以试试这个。找到PrintArea，然后使用Intersect遍历单元格，找出哪些单元格不在PrintArea中，Union这些单元格，然后在最后删除它们。通过这种方式，您可以同时删除不属于PrintArea的所有内容。希望这能有所帮助：

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

Answer 3

您可以使用范围的Column和Row属性来确定它的起始位置，如下所示

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

这样叫它

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