Why no borders show up?

Question

Dim rng2 As Range:
Set rng2 = ActiveSheet.Range("D:E", ActiveSheet.Cells.End(xlUp))
 
 With rng2
 .HorizontalAlignment = xlLeft
 .Borders.LineStyle = xlContinous
   
 End With

I understand my code like this.

rng2 is assigned to the Range "D:E" on the ActiveSheet. All Cells count to rng2 as long no blank cell occurs (End(xlUp))
With rng2 i assign to my Range rng2 the above mentioned settings which are left arrangement which works and Borders which shall in a continously Syle frame my rng2 - this does not work

I have to say that the first cell of D:E includes a value in D1 (header) but not in E1 (no header) is this the problem?

EDIT 17.05.21 full code

Sub Duplicate()

Dim nA As Long, nD As Long, i As Long, rc As Long
Dim s As String, j As Long

    Range("A:A").Copy Range("D1")
    Range("B1").Copy Range("E1")
    Range("D:D").RemoveDuplicates Columns:=1, Header:=xlYes
    rc = Rows.Count
    nA = Cells(rc, 2).End(xlUp).Row
    nD = Cells(rc, 4).End(xlUp).Row
    
       For i = 2 To nD
        v = Cells(i, 4)
        V2 = ""
        For j = 2 To nA
         If v = Cells(j, 1) Then
            V2 = V2 & Cells(j, 2) & ","
        End If
            
        Next j
        Cells(i, 5) = Mid(V2, 1)
    Next i

'neu
Dim rng2 As Range
Dim lastrow As Long

lastrow = ActiveSheet.UsedRange.Rows.Count

Set rng2 = ActiveSheet.Range("D1", "E" & lastrow)
 
 With rng2
 .HorizontalAlignment = xlLeft
 .Borders.LineStyle = xlContinuous
   
 End With

Debug.Print

    
End Sub

Kin Siang Kin Siang · Accepted Answer · 2021-05-17T11:48:05

A better approach should be like this:

Dim rng2 As Range
dim lastrow as long

lastrow = ActiveSheet.UsedRange.Rows.Count

Set rng2 = ActiveSheet.Range("D1", "E" & lastrow)
 
 With rng2
 .HorizontalAlignment = xlLeft
 .Borders.LineStyle = xlContinuous
   
 End With

In additional, there is a typo on your code,

xlContinous should be xlContinuous

Continue to sort your row for the border issue. Assuming you have 2 col with difference row value. Col F has 8 rows, while Col H has 5 rows:

In order to obtain lastrow for different row, you can use the code as following:

Sub t()

Dim lastrowCola As Long
Dim lastrowColb As Long

lastrowCola = Sheet1.Range("F1").End(xlDown).Row
Debug.Print lastrowCola

lastrowColb = Sheet1.Range("H1").End(xlDown).Row
Debug.Print lastrowColb

End Sub

So debug.print for lastrowCola is 8, while lastrowColb is 5. Based on your scenario, you can adjust accordingly.

Why no borders show up?

1 Answers