CODEWITHSUNDEEP
vbaexcel
TechGeek
TechGeek

Reputation: 2212

Get Actual Print Area of an Excel Sheet (Through VBA)

How can I get an actual Used Range (including charts) in Excel?

The problem is, user will NOT define any print area. But he should get a picture of all used range (including charts) in clipboard:

If I do like, Sheet1.PageLayout.PrintArea It gives me a blank string.

If I do like, Sheet1.UsedRange.Address, It doesn't consider the charts which are at bottom.

Please suggest a work-around.

Thanks.

Upvotes: 2

Views: 3631

Answers (1)

gembird
gembird

Reputation: 14053

Couldn't you use TopLeftCell and BottomRightCell of ChartObject and check if some of charts is outside of UsedRange? Like this:

Sub test()
    Dim usedRangeEx As Range
    Set usedRangeEx = GetUsedRangeIncludingCharts(Worksheets("Sheet1"))
    usedRangeEx.Activate
    Debug.Print usedRangeEx.Address
End Sub

Private Function GetUsedRangeIncludingCharts(target As Worksheet) As Range
    Dim firstRow As Long
    Dim firstColumn As Integer
    Dim lastRow As Long
    Dim lastColumn As Integer
    Dim oneChart As ChartObject

    With target
        firstRow = .UsedRange.Cells(1).Row
        firstColumn = .UsedRange.Cells(1).Column
        lastRow = .UsedRange.Cells(.UsedRange.Cells.Count).Row
        lastColumn = .UsedRange(.UsedRange.Cells.Count).Column

        For Each oneChart In .ChartObjects
            If oneChart.TopLeftCell.Row < firstRow Then _
                firstRow = oneChart.TopLeftCell.Row
            If oneChart.TopLeftCell.Column < firstColumn Then _
                firstColumn = oneChart.TopLeftCell.Column
            If oneChart.BottomRightCell.Row > lastRow Then _
                lastRow = oneChart.BottomRightCell.Row
            If oneChart.BottomRightCell.Column > lastColumn Then _
                lastColumn = oneChart.BottomRightCell.Column
        Next oneChart

        Set GetUsedRangeIncludingCharts = .Range(.Cells(firstRow, firstColumn), _
                                                 .Cells(lastRow, lastColumn))
    End With

End Function

Upvotes: 1

Related Questions