Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range use on page
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. -- Eric Howard Director of Process Automation Synergis Technologies, Inc. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range use on page
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 "Eric Howard" wrote: 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. -- Eric Howard Director of Process Automation Synergis Technologies, Inc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking for numbers in page range | Excel Worksheet Functions | |||
Printing Range at the beginning of every new page. | Excel Programming | |||
Refer to Range Name in Page Header | Excel Programming | |||
Selecting all contents on page (range) | Excel Programming | |||
How to Programmatically Insert a Page Break Every Nth Row in a Range | Excel Programming |