empty buffer for new iteration

[Steve]

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/communitie...c7d0&mid=01dde3a9-71b0-462a-84e4-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.

 

[Dave Peterson]

After correcting a typo (2 dots instead of one), this is the portion that resets
the grouping:

For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Borders(xlEdgeTop).LineStyle = xlSolid Then
Set TopCell = .Cells(iRow, "A")
Set BotCell = Nothing 'start looking

So if you don't have a topedge with a solid linestyle, the top cell won't get
reset.

 

[Matthew Herbert]

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]

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]

Steve,

Did you use the debugging tools that I mentioned? If you did, then you
would notice the observations listed below; if not, then you didn't take
advantage of tools that were designed to help you point out these types of
problems.

Applied the following borders:
R8 - Top Border Applied
R12 - Bottom Border Applied
R17 - Top Border Applied
R21 - Bottom Border Applied

Observation regaring applied borders:
R7 has a Bottom Border
R8 has a Top Border
R12 has a Bottom Border
R13 has a Top Border
R16 has a Bottom Border
R17 has a Top Border
R21 has a Bottom Border
R22 has a Top Border

Conclusion:
Yes, your data is becoming one big heap of merged cells. So, you can't have
your cake and eat it too in this situation.

Solution:
Create a range cell flag and test if the prevous cell was used as a bottom
cell and therefore shouldn't be set as the top cell.

I included the Debug.Print statements below. Click View, Immediate Window
from the VBE toolbar to see the Debug.Print statements, i.e. Debug.Print
prints to the Immediate Window. I also reset TopCell and BotCell after each
column loop. If the results are not what you expected, then please, change
the code to fit your needs.

Best,

Matt

Sub BorderLoops()

Dim iRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim TopCell As Range
Dim BotCell As Range
Dim BotCellFlag As Range
Dim Wks As Worksheet
'----------------------------------------
'With ActiveSheet
' FirstRow = 1
' LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'or whatever you want
'
'The firstrow was always 1
'The lastrow is the lastrow in column A that has something in it.
'
'(just an explanation that may help you on Monday)
'
'----------------------------------------

Set Wks = ActiveSheet

With Wks
FirstRow = 8
LastRow = 67 '250
FirstCol = 18
LastCol = 25 '.Cells(FirstRow, .Columns.Count).End(xlToRight).Column
'test run

For iCol = FirstCol To LastCol
For iRow = FirstRow To LastRow

If .Cells(iRow, iCol).Borders(xlEdgeTop).LineStyle = xlSolid Then
'Or xlDouble
Set TopCell = .Cells(iRow, iCol)

If Not BotCellFlag Is Nothing Then
If BotCellFlag.Address = TopCell.Address Then
Set TopCell = Nothing
End If
End If
If Not TopCell Is Nothing Then
Debug.Print "TopCell:"; TopCell.Address
Debug.Assert False
End If
Set BotCell = Nothing
Else
If .Cells(iRow, iCol).Borders(xlEdgeBottom).LineStyle =
xlSolid Then
If TopCell Is Nothing Then
'keep looking, because we're not in a "group"
Else
Set BotCell = .Cells(iRow, iCol)
Debug.Print "BotCell:"; BotCell.Address
Debug.Assert False
Set BotCellFlag = .Cells(iRow + 1, iCol)
Debug.Print "BotFlag:"; BotCellFlag.Address
Debug.Assert False
With Range(TopCell, BotCell)
Debug.Print "Merge :"; Range(TopCell,
BotCell).Address
Debug.Assert False
.Merge
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
End With
'get ready to start looking again
Set TopCell = Nothing
Set BotCell = Nothing
End If
End If
End If
Next iRow
Set TopCell = Nothing
Set BotCell = Nothing
Next iCol
End With
End Sub

 

[Steve]

Hi Matt.
I've pulled my macro, and have printed it out so I can go through with
greater clarity.
I've also placed your comments in it so I can view the specific items you've
mentioned.
I'm about done for the day, so I'll need to get back to this in the morning.

Thank you for the help so far.
I'm in the GMT-8 timezone, so please look for a repost tomorrow morning
(well, my morning anyway...) around 1600 GMT.

 

[Steve]

Hi Matt.
Quick response before I leave.
I ran through the sample you'd provided, with F8, and have noticed something
interesting.
I have the borders set in my sample at every 4 rows.
I set the top border, drop four rows, and set my bottom border.
I then drop four more rows, and set my next bottom border.
I repeat this last item through to the end of my data field.

The code you provided will iterate through, and select the first 4 rows, and
merge-- exactly as desired.
It then iterates through the next four rows, and does nothing.
It then looks at the next row's border as a top border, interates through
the four, and merges them.
Essentially what I've found is that it merges 4, skips 4, merges 4, skips 4,
etc.....

Because this looks like a border issue, I clicked on format cells, and
checked to see if the bottom border from the previous cell-- above-- is
listed as a top border for my cell of interest, and it is.

This raises something that I'm curious about--
Does VBA code view the borders the same as the excel program does?

I.e., the bottom border for row 11 is technically the top border for row 12.
Does VBA view it as such?
If not, then is there code that would "force" the border at the bottom of
11, to be viewed as the top border of 12?


I hope this is clear.... if not, please let me know.
Good night.
And again-- thank you for your help.

 

[Matthew Herbert]

Steve,

As noted in my observations, and per your confirmation, a Top border for one
cell is a Bottom border for the cell above it. I'm not aware of a way to
"force" the border to be read as a Top or Bottom border (see the code below
as an attempt to delineate top/bottom borders). This is why I created the
BotCellFlag range object. If the "flag" doesn't do what you want, then
you'll have to determine another method to decipher the Top/Bottom border
delineation.

Best,

Matt

This code returns the same cell address for both the Top and Bottom border.

Dim Brds As Borders
Set Brds = TopCell.Borders
Debug.Print Brds.Item(xlEdgeTop).Parent.Address
Debug.Print Brds.Item(xlEdgeBottom).Parent.Address

 

[Steve]

Hi Matt.
I can live with that.
I'll go back through and using this new code see what I can decipher.
Have a great weekend.
Best,
and again-- thank you.
SteveB.