Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
merge cells
Morning all.
Let's see.... I've modified an existing macro to test for borders, and to merge the selected cells. My If test appears to work ok, but my core operation doesn't. My code is: Code:
For Each rcell2 In Selection If rcell2.Borders(xlEdgeBottom).LineStyle = xlSolid Then ActiveSheet.Range(rcell1, rcell2).Select With Selection .Merge .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenter End With 'Set rcell1 = Nothing 'Set rcell2 = Nothing ActiveCell.Offset(1, 0).Select 'end if End If Next rcell2 I have 3 if tests outside of the above. My intention was to iterate through a series of rows, looking for a border on top, and a border on the bottom, after an undefined number of rows. Once it finds the rows, I want to select them, and merge them. However, in the case of my present macro, it merges, iterates through a second set of rows, and selects the first set, and then selects, and merges the second set with the first set. This does not work. My thinking at this point is that the rcell1 variable has the first row's location still stored in its buffer, and if this indeed the case, I need the rcell1 buffer emptied, to start with a new row location. The problem is-- I'm not clear on how this is done. Anyone know how to do this? Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
merge cells
I'd loop through the rows (not the selection).
Dim iRow as long dim FirstRow as long dim LastRow as long dim TopCell as range dim BotCell as range with activesheet firstrow = 1 lastrow = .cells(.rows.count,"A").end(xlup).row 'or whatever you want for irow = firstrow to lastrow if .cells(irow,"A")..Borders(xlEdgeTop).LineStyle = xlSolid then set topcell = .cells(irow,"A") set botcell = nothing 'start looking else if .cells(irow,"A").borders(xledgebottom).linestyle = xlsolid then if topcell is nothing then 'keep looking, because we're not in a "group" else set botcell = .cells(irow,"A") with .range(topcell,botcell) .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 end with untested, uncompiled. Watch out for typos. I used column A to look for those borders. Steve wrote: Morning all. Let's see.... I've modified an existing macro to test for borders, and to merge the selected cells. My If test appears to work ok, but my core operation doesn't. My code is: Code:
For Each rcell2 In Selection If rcell2.Borders(xlEdgeBottom).LineStyle = xlSolid Then ActiveSheet.Range(rcell1, rcell2).Select With Selection .Merge .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenter End With 'Set rcell1 = Nothing 'Set rcell2 = Nothing ActiveCell.Offset(1, 0).Select 'end if End If Next rcell2 I have 3 if tests outside of the above. My intention was to iterate through a series of rows, looking for a border on top, and a border on the bottom, after an undefined number of rows. Once it finds the rows, I want to select them, and merge them. However, in the case of my present macro, it merges, iterates through a second set of rows, and selects the first set, and then selects, and merges the second set with the first set. This does not work. My thinking at this point is that the rcell1 variable has the first row's location still stored in its buffer, and if this indeed the case, I need the rcell1 buffer emptied, to start with a new row location. The problem is-- I'm not clear on how this is done. Anyone know how to do this? Thank you. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
merge cells
Hi Dave,
Ok, so are you saying I should select an entire range, and then use this code to iterate through, looking for borders, and it should only merge the ranges with borders at top and bottom? "Dave Peterson" wrote: I'd loop through the rows (not the selection). Dim iRow as long dim FirstRow as long dim LastRow as long dim TopCell as range dim BotCell as range with activesheet firstrow = 1 lastrow = .cells(.rows.count,"A").end(xlup).row 'or whatever you want for irow = firstrow to lastrow if .cells(irow,"A")..Borders(xlEdgeTop).LineStyle = xlSolid then set topcell = .cells(irow,"A") set botcell = nothing 'start looking else if .cells(irow,"A").borders(xledgebottom).linestyle = xlsolid then if topcell is nothing then 'keep looking, because we're not in a "group" else set botcell = .cells(irow,"A") with .range(topcell,botcell) .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 end with untested, uncompiled. Watch out for typos. I used column A to look for those borders. Steve wrote: Morning all. Let's see.... I've modified an existing macro to test for borders, and to merge the selected cells. My If test appears to work ok, but my core operation doesn't. My code is: Code:
For Each rcell2 In Selection If rcell2.Borders(xlEdgeBottom).LineStyle = xlSolid Then ActiveSheet.Range(rcell1, rcell2).Select With Selection .Merge .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenter End With 'Set rcell1 = Nothing 'Set rcell2 = Nothing ActiveCell.Offset(1, 0).Select 'end if End If Next rcell2 I have 3 if tests outside of the above. My intention was to iterate through a series of rows, looking for a border on top, and a border on the bottom, after an undefined number of rows. Once it finds the rows, I want to select them, and merge them. However, in the case of my present macro, it merges, iterates through a second set of rows, and selects the first set, and then selects, and merges the second set with the first set. This does not work. My thinking at this point is that the rcell1 variable has the first row's location still stored in its buffer, and if this indeed the case, I need the rcell1 buffer emptied, to start with a new row location. The problem is-- I'm not clear on how this is done. Anyone know how to do this? Thank you. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
merge cells
Nope.
I'm saying that you should figure out the first and last row and change that in the code--unless it can determine it based on something else. And I'm saying that you'll have to change the column to what you need--I used column A. Steve wrote: Hi Dave, Ok, so are you saying I should select an entire range, and then use this code to iterate through, looking for borders, and it should only merge the ranges with borders at top and bottom? "Dave Peterson" wrote: I'd loop through the rows (not the selection). Dim iRow as long dim FirstRow as long dim LastRow as long dim TopCell as range dim BotCell as range with activesheet firstrow = 1 lastrow = .cells(.rows.count,"A").end(xlup).row 'or whatever you want for irow = firstrow to lastrow if .cells(irow,"A")..Borders(xlEdgeTop).LineStyle = xlSolid then set topcell = .cells(irow,"A") set botcell = nothing 'start looking else if .cells(irow,"A").borders(xledgebottom).linestyle = xlsolid then if topcell is nothing then 'keep looking, because we're not in a "group" else set botcell = .cells(irow,"A") with .range(topcell,botcell) .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 end with untested, uncompiled. Watch out for typos. I used column A to look for those borders. Steve wrote: Morning all. Let's see.... I've modified an existing macro to test for borders, and to merge the selected cells. My If test appears to work ok, but my core operation doesn't. My code is: Code:
For Each rcell2 In Selection If rcell2.Borders(xlEdgeBottom).LineStyle = xlSolid Then ActiveSheet.Range(rcell1, rcell2).Select With Selection .Merge .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenter End With 'Set rcell1 = Nothing 'Set rcell2 = Nothing ActiveCell.Offset(1, 0).Select 'end if End If Next rcell2 I have 3 if tests outside of the above. My intention was to iterate through a series of rows, looking for a border on top, and a border on the bottom, after an undefined number of rows. Once it finds the rows, I want to select them, and merge them. However, in the case of my present macro, it merges, iterates through a second set of rows, and selects the first set, and then selects, and merges the second set with the first set. This does not work. My thinking at this point is that the rcell1 variable has the first row's location still stored in its buffer, and if this indeed the case, I need the rcell1 buffer emptied, to start with a new row location. The problem is-- I'm not clear on how this is done. Anyone know how to do this? Thank you. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
merge cells
Ok, column A, I'm fine with.
Not an issue. It's the something else, with the first and last rows that I'm stuck on. Let's revisit this on Monday. All my code is at the office, and I'm posting from home now. I'm hoping that something will gel with this in my mind over the weekend. "Dave Peterson" wrote: Nope. I'm saying that you should figure out the first and last row and change that in the code--unless it can determine it based on something else. And I'm saying that you'll have to change the column to what you need--I used column A. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
merge cells
In the code I suggested, I used this:
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) Steve wrote: Ok, column A, I'm fine with. Not an issue. It's the something else, with the first and last rows that I'm stuck on. Let's revisit this on Monday. All my code is at the office, and I'm posting from home now. I'm hoping that something will gel with this in my mind over the weekend. "Dave Peterson" wrote: Nope. I'm saying that you should figure out the first and last row and change that in the code--unless it can determine it based on something else. And I'm saying that you'll have to change the column to what you need--I used column A. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically merge mulitiple cells to one cells | Excel Worksheet Functions | |||
Select Merged Cells and Unmerge Spread Merge Data To All Cells | Excel Programming | |||
how do I merge cells into one then delete the original cells? | Excel Worksheet Functions | |||
How can I have formatting options like merge cells ,Bold,active for the unlocked cells of the protected worksheet.Is it possible in excel? | Excel Programming | |||
How do I merge cells in Excel, like just 2 cells to make one big . | Excel Discussion (Misc queries) |