We help IT Professionals succeed at work.

Create Border Line

Cartillo
Cartillo asked
on
Hi Experts,

I would like to request Experts help. The attached script being used for merge the cells at Week1 to Week5. However, after completion of the macro the last row of each Week sheets without border line.  I have attached the workbook for Experts  perusal. Hope Experts could help me to fix this border line issue.


Sub MergeCells()
Dim c As Range, firstaddress As String, rng1 As Range, rng2 As Range
Dim I As Long, asht As Worksheet

Application.DisplayAlerts = False

Set asht = ActiveSheet
For I = 1 To 5

    Sheets("Week" & I).Activate
    Set rng1 = Sheets("Week" & I).Range("B4:H291")
    With rng1
        Set c = .Find("*")
        If Not c Is Nothing Then
            firstaddress = c.Address
            Do
                If c.Offset(1) = "" Then
                    If c.End(xlDown).Row <> Rows.Count Then
                        Set rng2 = Range(c, c.End(xlDown).Offset(-1))
                    Else
                        Set rng2 = Range(c, Cells(rng1.Cells(rng1.Cells.Count).Row, c.Column))
                    End If
                    With rng2
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlCenter
                        .WrapText = True
                        .MergeCells = True
                        
                    End With
                End If
                Set c = .FindNext(c)
            'Loop While Not c Is Nothing And c.Address <> firstaddress
            Loop While c.Address <> firstaddress
        End If
    End With
    With rng1
        .Font.Size = 9
        .Font.Bold = True
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Color = RGB(100, 100, 100)
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Color = RGB(100, 100, 100)
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Color = RGB(100, 100, 100)
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Color = RGB(100, 100, 100)
        End With
    End With
Next I
asht.Activate
Application.DisplayAlerts = True

End Sub

Open in new window

Border-Line.xls
Comment
Watch Question

Looks like rows are hidden at the bottom. The hidden rows have borders as desired. Why are these cells hidden?

Author

Commented:
Hi MCSolutions03,

This is to ensure only valid rows are displayed (with data). Is that a way to unhide the last row of the this hidden row automatically whenever I run this "MergeCells" sub?
you can run this segment after processing each sheet

    lastrow = (Range("A65536").End(xlUp).Row) +1
    Rows(lastrow).Select
    Selection.EntireRow.Hidden = False

Open in new window

It selects the very last row of data and unhides it.

Author

Commented:
Hi MCSolutions03,

Any chance to integrate this line in "Sub MergeCells()"?
yes, put these lines after line 55 in your posted code above.
kgerbChief Engineer

Commented:
Cartillo,
I think it should be noted that MCSolutions03's code will on find correctly find the last row of data if the data is in column A.  To find the last row regardless of what column it is in use something like this.
lrow = Cells.Find("*", LookIn:=xlValues, searchdirection:=xlPrevious).Row

Open in new window

Kyle
yes, my apologies.

kgerb, that is a much better method. Thanks.
Chief Engineer
Commented:
Cartillo,
I had a thought (which is rare).  Instead of trying to unhide rows in order to get the borders to show up, put this code between lines 54 and 55 in your routine MergeCells().

        With .Offset(1).Rows(.Rows.Count)
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Color = RGB(100, 100, 100)
            End With
        End With

Open in new window

I think it will give you want you want.

Kyle
What I was seeing is that the hidden rows hide the remainder of a merged cell. So the entire merged cell spans from say b73 to b93 but rows 80-93 are hidden. So the border won't show because of the merge.

Author

Commented:
Hi,

Thanks for the help
kgerbChief Engineer

Commented:
Let's say you have a merged cell from A1 to A10.  You select the merged cell and add a bounding border to it.  Now you hide rows 6-10 (for example).  The bottom border disappears.  Now, unhide the rows and delete the existing border.  Reapply the border but instead of applying it to all four sides of the merged call, apply it to the left, top, and right sides only.  Then apply a top border to cell A11.  The result looks the same but now when you hide rows 6-10 the bottom border does not disappear.  That's essentially what I did with the extra code.

Kyle
Ah, I see. Awesome!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.