Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array of page ranges
Hello.
I need a function or subroutine that will return an array of the page ranges on the currently active sheet. The number of pages is variable, but they always involve horizontal page breaks only (i.e. no vertical breaks). They may include both natural breaks (Excel imposed) as well as manual page breaks (so I can't just count the number of rows) to the next break. The print area is always set. So, as an example, if the set print area is "A3:J111" my array should contain a list that looks like the following: 1st Element = "A3:J53" 2nd Element = "A54:J105" 3rd Element = "A106:J111" My attempted code was so bad, I trashed it. Your example code is what I need. Please help if you can. Thanks much in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array of page ranges
See if this helps
It is generally agreed that using xlm (xl4) macro commands is better at this then VBA. Here is one way to do get an array that holds the pagebreak locations: Function GetHorizontalPageBreak(sh As Worksheet, wb As Workbook) Dim horzpbArray As Variant Dim brkType As String Dim i As Long 'varr = Evaluate("INDEX(hzPB,COLUMN(A:IV),0)") wb.Names.Add Name:="hzPB", _ RefersToR1C1:="=GET.DOCUMENT(64,""" & _ sh.Name & """)" horzpbArray = Application.Evaluate("Index(hzPB,Column(A:IV),0)") wb.Names("hzPB").Delete GetHorizontalPageBreak = horzpbArray End Function Sub TestHPageBreak() Dim hpgbrk As Variant Dim sht As Worksheet Set sht = ActiveSheet hpgbrk = GetHorizontalPageBreak(sht, sht.Parent) For i = UBound(hpgbrk) To LBound(hpgbrk) Step -1 If sht.Rows(hpgbrk(i)).PageBreak = xlPageBreakManual Then Debug.Print i, hpgbrk(i), "Manual" Else Debug.Print i, hpgbrk(i), "Automatic" End If Next End Sub Regards, Tom Ogilvy pcrobinson wrote in message ... Hi I need to scan down a column and locate the presence of horizontal page breaks to add data to the cells immediately below the page breaks. How programmatically can I know where they are? Thanks for any help, Peter Robinson -- Don Guillett SalesAid Software "mik" wrote in message ... Hello. I need a function or subroutine that will return an array of the page ranges on the currently active sheet. The number of pages is variable, but they always involve horizontal page breaks only (i.e. no vertical breaks). They may include both natural breaks (Excel imposed) as well as manual page breaks (so I can't just count the number of rows) to the next break. The print area is always set. So, as an example, if the set print area is "A3:J111" my array should contain a list that looks like the following: 1st Element = "A3:J53" 2nd Element = "A54:J105" 3rd Element = "A106:J111" My attempted code was so bad, I trashed it. Your example code is what I need. Please help if you can. Thanks much in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array of page ranges
Mik,
Try something like the following: Sub AAA() Dim SRng As Range Dim ERng As Range Dim HPB As HPageBreak Dim WS As Worksheet: Set WS = ActiveSheet Dim Arr() As Range Dim Ndx As Long: Ndx = 1 ReDim Arr(1 To WS.HPageBreaks.Count + 1) Set SRng = Range("A1") For Each HPB In WS.HPageBreaks Set ERng = HPB.Location(0, 1) Set Arr(Ndx) = Range(SRng, ERng) Set SRng = HPB.Location Ndx = Ndx + 1 Next HPB Set Arr(UBound(Arr)) = Range(ERng(2, 1), Cells(Rows.Count, "A")) For Ndx = LBound(Arr) To UBound(Arr) Debug.Print Arr(Ndx).Address Next Ndx End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "mik" wrote in message ... Hello. I need a function or subroutine that will return an array of the page ranges on the currently active sheet. The number of pages is variable, but they always involve horizontal page breaks only (i.e. no vertical breaks). They may include both natural breaks (Excel imposed) as well as manual page breaks (so I can't just count the number of rows) to the next break. The print area is always set. So, as an example, if the set print area is "A3:J111" my array should contain a list that looks like the following: 1st Element = "A3:J53" 2nd Element = "A54:J105" 3rd Element = "A106:J111" My attempted code was so bad, I trashed it. Your example code is what I need. Please help if you can. Thanks much in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array of page ranges
You can use the Hpagebreaks collection to do this.
X = Activesheet.HpageBreaks.Count will give you the number of page breaks. You can then cycle through the collection and get the addresses of the top left cell on each page using: Activesheet.HPageBreaks(X).Location.Address -----Original Message----- Hello. I need a function or subroutine that will return an array of the page ranges on the currently active sheet. The number of pages is variable, but they always involve horizontal page breaks only (i.e. no vertical breaks). They may include both natural breaks (Excel imposed) as well as manual page breaks (so I can't just count the number of rows) to the next break. The print area is always set. So, as an example, if the set print area is "A3:J111" my array should contain a list that looks like the following: 1st Element = "A3:J53" 2nd Element = "A54:J105" 3rd Element = "A106:J111" My attempted code was so bad, I trashed it. Your example code is what I need. Please help if you can. Thanks much in advance. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array of page ranges
Okay, I will pick these suggestions apart and am confident
I can come up with a solution. Thanks much to all who responded. -----Original Message----- Hello. I need a function or subroutine that will return an array of the page ranges on the currently active sheet. The number of pages is variable, but they always involve horizontal page breaks only (i.e. no vertical breaks). They may include both natural breaks (Excel imposed) as well as manual page breaks (so I can't just count the number of rows) to the next break. The print area is always set. So, as an example, if the set print area is "A3:J111" my array should contain a list that looks like the following: 1st Element = "A3:J53" 2nd Element = "A54:J105" 3rd Element = "A106:J111" My attempted code was so bad, I trashed it. Your example code is what I need. Please help if you can. Thanks much in advance. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
How can Enter Page Numbers and/or page ranges separated by commas. | Excel Discussion (Misc queries) | |||
Use named ranges in array formula | Excel Worksheet Functions | |||
Creating an array from non-contiguous ranges | Excel Programming | |||
how to assign ranges on different sheets to an array | Excel Programming |