Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default Need to get a Page Number for a Cell

I have a large worksheet that I had been merging cells based if they contain the same information. The problem is, the document could be super long and I am printing it to pdf. So if there are multiple cells in a row vertically that are the same, you might not see that text on one page. I am wanting to be able to tell if the rows are on the same page before merging them.. Is this possible?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default Need to get a Page Number for a Cell

Chris Scott wrote:

I have a large worksheet that I had been merging cells based if they
contain the same information. The problem is, the document could be
super long and I am printing it to pdf. So if there are multiple cells
in a row vertically that are the same, you might not see that text on
one page. I am wanting to be able to tell if the rows are on the same
page before merging them. Is this possible?


Finding the page number *should* be irrelevant if you're merging content. (I
don't *think* you can retrieve the info you want, but I'm not an expert.)

Without knowing exactly what you're doing, it's a bit hard to say, but
here's what I use to find duplicates. Modify as needed.

'compare selected cell to all below
For L0 = ActiveCell.Row + 1 To _
ActiveCell.SpecialCells(xlCellTypeLastCell).Row
If Cells(L0, ActiveCell.Column).Value = ActiveCell.Value Then
[your option he select found entry, mark it somehow (bold?),
delete it, merge via code, whatever]
End If
Next

Select a cell and then run it.

You could further automate this by adding a second loop to step down the
list, like this:

'compare each cell in column to all below
For L0 = ActiveCell.Row to _
ActiveCell.SpecialCells(xlCellTypeLastCell).Row - 1
For L1 = L0 + 1 to ActiveCell.SpecialCells(xlCellTypeLastCell).Row
If Cells(L1, ActiveCell.Column).Value = _
Cells(L0, ActiveCell.Column).Value Then
[your option here, as above]
End If
Next
Next

--
Do you recognize my face? I'm the one you left behind!
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default Need to get a Page Number for a Cell

I think I have found what I need to do. I need to determine when each page break is on the sheet. Then go from there.
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Need to get a Page Number for a Cell

Hi Chris,

Am Wed, 11 May 2016 10:26:01 -0700 (PDT) schrieb Chris Scott:

I think I have found what I need to do. I need to determine when each page break is on the sheet. Then go from there.


try:

Sub PageNumber()
Dim varPN() As Variant
Dim rngC As Range
Dim HPB As HPageBreak
Dim i As Long, j As Long, LRow As Long, First As Long, Last As Long
Dim wsh As Worksheet

Application.DisplayAlerts = False
'Modify sheet name
Set wsh = ThisWorkbook.Sheets("Sheet1")
With wsh
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'Modify PrintArea
.PageSetup.PrintArea = "A1:G" & LRow

For Each HPB In .HPageBreaks
ReDim Preserve varPN(i)
varPN(i) = HPB.Location.Row
i = i + 1
Next
'Change i to 3 if you have headers
For i = 2 To LRow
First = 0: Last = 0
If .Cells(i, 1) = .Cells(i - 1, 1) Then First = i - 1
If First 0 Then Last = Application.CountIf(.Range("A:A"), .Cells(i, 1)) + First - 1
If First 0 And Last 0 Then
For j = LBound(varPN) To UBound(varPN)
If First < varPN(j) And Last <= varPN(j) Then
.Range(.Cells(First, 1), .Cells(Last, 1)).Merge
i = Last + 2
Exit For
ElseIf First < varPN(j) And Last varPN(j) Then
.Range(.Cells(First, 1), .Cells(varPN(j) - 1, 1)).Merge
i = varPN(j)
Exit For
End If
Next
End If
Next
End With
Application.DisplayAlerts = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Page Number in cell Marilyn Excel Discussion (Misc queries) 3 July 17th 08 01:06 AM
How to display current page number in any cell of that page. Laljeet Excel Discussion (Misc queries) 8 February 2nd 08 02:31 AM
sheet tabs as page number and in a cell page of pages? [email protected] Excel Discussion (Misc queries) 0 November 22nd 05 03:43 PM
Page Number of a cell [email protected] Excel Worksheet Functions 2 October 26th 05 12:07 PM
inserting page number in cell David White Excel Programming 2 October 20th 03 02:56 AM


All times are GMT +1. The time now is 08:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"