find range of covered cells by Excel charts

[dbqp]

In Excel XP, I've got a macro running to:
1) switch to/from forumla view
2) autofit columns and rows in the used area
3) reset page breaks
4) set the print area and force fit to one landscaped page

It works great, except for leaving charts off of the print area.

I need to figure out what range (or at least the last row) is covered by a chart so I can move the page break below that row.

I have rooted around and found lots of examples of how to make a chart cover a specific range of cells, but what I need is the reverse.

Here's what I've worked up so far. I'm trying to reverse some of the code that I've found, but it's really not working out for me:

Sub CheckChartSize()

Dim iCharts As Integer
Dim objChart As ChartObject
Dim myRange As Range

Set myRange = ActiveSheet.Range("A1:B2")

For iCharts = 1 To ActiveSheet.ChartObjects.Count

ActiveSheet.ChartObjects(iCharts).Activate

Set objChart = ActiveChart.Parent

'these don't work:

myRange.Width = objChart.Width
myRange.Left = ActiveChart.ChartArea.Left
myRange.Height = ActiveChart.Parent.Height

myRange.Width = objChart.Width
myRange.Top = objChart.Top
myRange.Left = objChart.Left

MsgBox myRange

Next iCharts

End Sub

 

[SkipVought]

Hi,

The ChartObject object has these properties...
[tt]
TopLeftCell
Top
Left
Width
Height
[/tt]
I guess I'm having a problem visualizing your problem. First off, why are you setting all these print-related parameters via code? Is the data on the sheet that volatile that you could not pre-set the print area, and the position of the chart?

Short of that, you can calculate the Width & Height of the Print Area, and you can change the Top, Left, Width & Height parameters of the ChartObject Object in order to fit.

For instance...

With ActiveSheet
  With .[Print_Area]
    nTop = .top
    nLeft = .left
    nWidth = .width
    nHeight = .height
  End With
  With.ChartObjects(1)
    .top = YouPick
    .left= nleft
    .width = nWidth
    .height = thirdPick
  end with
End with

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[dbqp]

I'm trying to make printing a spreadsheet, with or without charts, take as little time as possible by automating and standardizing student work files, as much as is possible via a simple keypress (macro assigned to a key).

So I need to read the location of the chart and make sure that my print area includes it. Thus reading the cells covered, rather than setting a size.

Students are responsible for creating and formatting the charts (and that's the focus of the lesson) so I don't want to resize it in the macro.

I guess a more clear statement of my question would be, "how can I find the range of cells covered by a chart from a ChartObject?

 

[SkipVought]

Same principle.

if your print range width is less than the chart left + width, it's not wide enough. It's gotta be at least that wide. same with height, using top.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[dbqp]

Sorry, I don't think I've clearly stated that I'm talking about an embedded chart - not one on a separate worksheet.

I'm working with a variety of assignments which have embedded charts and a variable amount of data.

That's why I'm setting the print area to the range of used cells - but I need also to include the embedded charts.

I need to add the area covered by the embedded chart to the range of used cells.

 

[SkipVought]

I'm very clear on it being an embedded chart.

That's why I'm setting the print area to the range of used cells - but I need also to include the embedded charts.

Why are you setting a Print Area at all, if you are dealing with used range?

Clear the Print Area

In Print Preview, set your Page scaling to Fit to... whatever is appropriate. It WILL include whatever chart(s) are on your sheet.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[dbqp]

I REALLY wish that worked...it would have saved me a ton of time trying to find a work around.

I'm setting a Print Area because apparently if a student has inadvertently changed the contents or formatting of a cell outside the actual used range (which does happen a lot) then those corresponding rows and columns are also included in the scaled page (even if those cells are "empty").

That behavior very often results in formulas that are too small to read on a printed copy - thus my macro to set the "real" used area as the print area.

The only way to stop this behavior is to select all the columns and rows that show on the printed copy (but aren't actually being used) and then manualy clear formats and contents. Doing that programatically didn't seem to be any smarter a solution.

Thanks for the help...

 

[SkipVought]

So how are you defining the used range?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[dbqp]

When I realized that ActiveSheet.UsedRange.Select was also including those exteraneous cells, I did some searching and ended up using DRJ's code at VBAExpress.



Option Explicit

Public Function RealUsedRange() As Range

Dim FirstRow As Long
Dim LastRow As Long
Dim FirstColumn As Integer
Dim LastColumn As Integer

On Error Resume Next

FirstRow = Cells.Find(What:="*", After:=Range("IV65536"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

FirstColumn = Cells.Find(What:="*", After:=Range("IV65536"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

LastColumn = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn))

On Error Goto 0

End Function

 

[SkipVought]

    Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn))
    With RealUsedRange
       Range( _
            Cells(.Row + .Rows.Count, 1), _
            Cells(Cells.Rows.Count, 1)).EntireRow.Delete xlShiftUp
       Range( _
            Cells(1, .Column + .Columns.Count), _
            Cells(1, Cells.Columns.Count)).EntireColumn.Delete xlShiftleft
    End With

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}