Count merged rows in first column

[vivekdevkar]

Hi All,


I want to count the number of merged visible rows (i.e) if four rows are merged then it should be count as 1, and if the count reaches 13, I have to insert a horizontal pagebreak automatically.

Please note that some merged rows are hidden in my case, so it should count only the visible merged rows and give the count.

Please help in VB code.


Regards,
vivek.

Thanks in Advance.

[Domski]

VBA and merged cells really don't get on. The best advice I could give you would be not to use them.

Dom

[TMS]

If you merge every 4 cells in column A and then put some numbers in the merged cells (say 1 to 16), it will appear as though the number is at the bottom (depending on your formatting). However, it is actually in the first cell of each group of merged cells.

Try this little example:

Please Login or Register  to view this content.

It's also interesting that, if I put an Autofilter on and filter numbers less than or equal to 13, I only get the cells with the numbers, not the merged cell block.

However, there's a clue there. Try:

Please Login or Register  to view this content.

Just be wary of header rows confusing the count

Regards

[TMS]

Oh, I should also have said, I avoid merged cells like the plague as they screw up alls sorts of things.

People tend to use them when they're trying to make the worksheet look pretty and are trying to avoid repetition of part numbers, or whatever. I tend to use conditional formatting for that and it means I keep all the data I need and can, if I want, sort the data without it getting totally FUBAR'd

Regards

[JBeaucaire]

Select the range of cells to evaluate in the first column then run this macro:

Please Login or Register  to view this content.

[vivekdevkar]

Thanks for helping me..

I have selected a range only in first column....
But still i am getting error in highlighted line...

ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=RNG.Cells(MyRow)

The error is

Runtime error 1004

This action exceeds the number of pagebreaks you can add manually add to a worksheet.


Thanks

[JBeaucaire]

Um...I'm not sure what you're asking. The error is telling you have reached your maximum pagebreaks. So, what next? A maximum is a maximum.