Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-28-2012, 04:32 AM
gsrikanth gsrikanth is offline merge cells Windows XP merge cells Office XP
Competent Performer
merge cells
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default merge cells


Range("A1", Range("A" & ActiveSheet.HPageBreaks(1).Location.Row - 1)).Merge

merging come to only first page i need to all

- What is/are the type(s) of the page breaks; automatically inserted by Excel, manually inserted or both?

- You said you have 161 pages. In this case (from my experience) Excel will automatically insert 160 page breaks and don't set the last page break unless the last row in the last page is used.

My question is, do you want to merge the 1st column of last page despite how much of the page is filled with data? Please explain how do you want to do it. E.g., an option would be merging until the last used row. Another option would be merging certain number of rows, etc
1) page breaks are automatically inserted and manually
2) unless the last row in the last page is used
3) merge until the last used row
attachment for your reference
(manual prepaired)
Attached Files
File Type: xlsx Book131.xlsx (57.3 KB, 9 views)
Reply With Quote
  #2  
Old 03-02-2012, 10:58 PM
macropod's Avatar
macropod macropod is offline merge cells Windows 7 64bit merge cells Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Your whole post looks as if it's a reply to a post somewhere else. Very confusing.

To merge all of column A, you don't need to know about page breaks. You could use code like:
Code:
Sub Demo()
With ActiveSheet
  .Range("A1", Range("A" & .Cells.SpecialCells(xlCellTypeLastCell).Row)).Merge
End With
End Sub
If you do that to your attached worksheet, though, it'll wipe out all the data, as row 1 is already merged and the result will be the whole of the used range being merged into one cell. Given those considerations, it is not at all clear which range(s) you want to merge. The following macro has code for processing both horizontal and vertical ranges on a page-by-page basis. At present, though, it only displays what those ranges would be, via a message box.
Code:
Sub Demo()
Dim h As Long, i As Long, j As Long, k As Long
With ActiveSheet
  h = .Cells.SpecialCells(xlCellTypeLastCell).Column
  j = 1
  For i = 1 To .HPageBreaks.Count
    If i > 1 Then j = .HPageBreaks(i - 1).Location.Row
    k = .HPageBreaks(i).Location.Row - 1
    MsgBox .Range("A" & k, .Cells(k, h).Address).Address _
      & vbCr & .Range("A" & j, "A" & k).Address
    '.Range("A" & j, .Cells(j, h).Address).Merge
    '.Range("A" & j, "A" & k).Merge
  Next
End With
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
merge cells Macro to Merge Cells Row By Row in a Table KD999 Word VBA 4 02-20-2012 08:51 PM
merge cells Concatenate/merge contents of multiple cells PSYloco Excel 2 05-14-2011 03:46 PM
Count range cells eliminating merge cells danbenedek Excel 0 06-15-2010 12:40 AM
How to merge matching cells vertically? Odiseh Excel 1 01-02-2010 02:41 PM
Nested if formulat to merge cells cristi22 Excel 0 10-23-2006 08:28 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:03 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft