Hi Matt.
Not the next column, but for each cell group.
I typically have a 4 cell group that gets merged.
With each iteration, the topcell is selected, and it then looks for the
botcell. Once the botcell is found, all cells from Topcell to Botcell are
merged. The macro drops to the next cell, finds the topCell border, then
iterates through to the botcell. It merges those. Then it grabs both the
initial cell group, and the last cell group, and merges all of them into a
single grouping. Thus, with each iteration my merged cell group merges into
increasingly larger groups until it hits the bottom of the worksheet.
1- it's supposed to only grab the cells where topcell has a border on the
top of the cell, and then botcell where the border is on the bottom of the
cell-- and merge those.
2-It then iterates through all one column looking for borders. Once it no
longer finds a border on either the top, or the bottom of a cell, it's
supposed to stop.

Hope that's more clear. If not, please let me know.



"Matthew Herbert" wrote:

Steve,

Are you saying that you want TopCell to be reset if you move to the next
column after searching the rows? You have the "verbage" to reset the object
in the code, i.e. Nothing. A range is an object, and objects are cleared
from memory (i.e. "reset") with the Nothing keyword. (Both ToCell and
BotCell are dimensioned as Range). If you want TopCell "reset" after each
column then add "TopCell = Nothing" prior to your "Next iCol" statement (also
shown below); otherwise, put the statment where you need it to go.

Also, you can test your code by debugging it (Debug | Step Into) via the F8
key. Hit F8 repeatedly, hover the cursor over your variables, watch the
Excel window as you hit F8, etc. Additionally, you can add Debug.Print
statements and view the Immediate Window (View | Immediate Window). For
example, you can add "If Not TopCell Is Nothing Then Debug.Print
TopCell.Address" to see the address of TopCell, or simply put "Debug.Print
.Cells(iRow, iCol).Address" somewhere within the code to see where you are in
the loop.

Next iRow
TopCell = Nothing
Next iCol

Best,

Matthew Herbert

"Steve" wrote:

Morning all.
Back in July I was having a conversation with Dave Peterson regarding the
merging of cells. All of the code, etc... is at this link.

http://www.microsoft.com/communities...4-4ad88ee76d9f

As I have worked through his code sample, I found that I ran across an issue
I wasn't anticipating.
It appears that the TopCell variable remains as a constant, and each new
BotCell variable is the variant. This results in my merged cell group growing
by the location of the botcell.
I.e., say that I start TopCell at a10. BotCell drops by 4 with each new
iteration. When my IF criteria are located, instead of TopCell being
relocated for each new iteration it remains at A10, and everything from
TopCell to BotCell is merged.
In the end, I get a merged cell group that is 1000's of rows in size. In
spite of my criteria stated in the IF EQ's.

My intention was to have the buffer emptied for TopCell, and BotCell, so
that they'd find the next set of criteria, and perform the group merge that I
want. It appears that the BotCell is indeed reset, but the TopCell remains at
its original position.

I have if eq's set to look for row border types. If the border is found at
the top of the cell, use that as my TopCell. it then looks for either a
bottom or a top border. If it finds a bottom border, it sets that as the
BotCell.

What verbage/term/phrase do I need to empty/reset the buffer for both
TopCell, and BotCell?

Thank you.