merge cells part deux

[Steve]

So as to ensure that this doesn't get lost from last Friday....

Dave Peterson writes:
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)


Ok, this is a working macro.
Below is the code for future reference-- for when someone like me comes
along and needs a similar macro.....

I made some modifications because of some issues I found that were counter
productive to what I needed.
1- I set my start and end rows/columns to hard values, because the use of a
variable made it look all the way out to the end of the rows, and columns...
2^20 rows, and 16*2^10 columns. While a great thing to keep handy, it was
looking at more than I needed for now.

2- I added a column subst as well because I wanted it to look through all of
the columns with matching criteria, and process the rows accordingly.

3- while a future rendition, I'll next add a worksheet iterator as well.
This way I'll be able to process an entire workbook without having to
manually go through each sheet. Again-- in the future.

Dave, thank you so much for your help...!
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
'----------------------------------------
'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)
'
'----------------------------------------


With ActiveSheet
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)
Set BotCell = Nothing 'start looking
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)
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
Next iCol
End With
End Sub

 

[Dave Peterson]

If I can figure out the range to be processed without asking the user, I find
that (usually) better.

But if you wanted, you could ask the user to select the range first:

with selection.areas(1) 'in case multiple areas
FirstRow = .row
LastRow = .rows(.rows.count).row
FirstCol = .column
LastCol = .column(.columns.count).column
end with

 

[Steve]

I agree.
This is part of what I'd initially wanted. But after using the original
version of a Per Jessen macro where I had this, I'd rather just have it look
at a range with borders-- w/o requesting user input-- and then process them
accordingly.

As I've thought about it, the range that I'd use this macro for is fairly
small-- generally 8-10 columns, and typically no more than 150 rows. As such,
because your initial version looked at all rows, and all columns in the
worksheet, I think that I'd rather set it manually, and if I have more than
250-300 rows, I'll do something different. Perhaps what you've shown below
here.
Again, thank you for your help.
Best.