Range use on page

[Guest]

Is there a way to determine the range of cells on a page using VBA?

I'm defining a page as the cells within a series of page breaks, automatic
or manual. So if it worksheet contains items in the first 3 rows and 5
columns, then usedrange property would return the first 3 rows and 5 columns.
However after a print preview Excel displays page breaks after column I and
after row 52. How can I determine this range through VBA, since these page
breaks do not appear through HPagebreaks or VPagebreaks.

 

[Guest]

Hi,
If i understand correctly, in the above example, you are trying to determine
the range A1:I52. That is, you need to detrmine the next 'potential' page
break.
I don't know of any direct way.
A messy and non-elegant way would be:
- Say there is 10 vertiocal page break
- Fill up row 1 with dummy data from the cell after the rightmost column of
the usedrange TO the last cell on that row on the sheet (IV1).
- Locate the new break (v page break 11)
- remove the dummy data
- do something similar to figure out the h page break

in vba, for v page break:
Sub test()
Dim wsh As Worksheet
Dim extRg As Range 'extended range = end of used range to end of
sheet
Dim currVBreaks As Long 'current number of vbreaks
Dim extVBreakCol As Long 'new extended vbreak's column

Set wsh = ActiveSheet
With wsh
currVBreaks = .VPageBreaks.Count
Set extRg =
..UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Offset(0, 1)
Set extRg = .Range(extRg, .Cells(1, 256)).Rows(1)
extRg.Formula = "="""""
extVBreakCol = .VPageBreaks(currVBreaks + 1).Location.Column
extRg.Formula = ""
End With

MsgBox extVBreakCol
End Sub

From there, you can determine the ranges.
Not very pretty since adding and removing dummy data 'modifies' the sheet,
but well... I hope this helps,
Sebastien