Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty buffer for new iteration
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty buffer for new iteration
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. 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. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty buffer for new iteration
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty buffer for new iteration
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty buffer for new iteration
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" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty buffer for new iteration
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. "Matthew Herbert" wrote: 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" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty buffer for new iteration
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty buffer for new iteration
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" wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty buffer for new iteration
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. "Matthew Herbert" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Buffer Overflow in Excel | Excel Worksheet Functions | |||
Buffer Overflow | Excel Programming | |||
Shift+Del buffer | Excel Programming | |||
Reading txt file into the buffer | Excel Programming | |||
buffer overflow | Excel Programming |