VBA - Dynamic range needs border

[ChrisFoster]

Hi all,

I have a dynamic range of rows across columns F:K, there are some cells in columns J & K though that may be blank at random points. What I need is some code that will put a border round all cells across the rows that has data in column F, then add a bold border round that whole table.

Is this even possible? If so, any help would be greatly appreciated.

Kind Regards,

Chris

 

[Yongle]

Assumes
- the "data" in F is NOT formula
- row1 is header row

Sub Border()
    Dim r As Long, c As Long
    Range("F2:F" & Rows.Count).SpecialCells(xlCellTypeConstants).BorderAround ColorIndex:=5, Weight:=xlThin
    For c = 6 To 11
        r = WorksheetFunction.Max(r, Cells(Rows.Count, c).End(xlUp).Row)
    Next
    Range("F1:K1").Resize(r).BorderAround ColorIndex:=23, Weight:=xlThick
End Sub

 

[ChrisFoster]

Assumes
- the "data" in F is NOT formula
- row1 is header row

Sub Border()
    Dim r As Long, c As Long
    Range("F2:F" & Rows.Count).SpecialCells(xlCellTypeConstants).BorderAround ColorIndex:=5, Weight:=xlThin
    For c = 6 To 11
        r = WorksheetFunction.Max(r, Cells(Rows.Count, c).End(xlUp).Row)
    Next
    Range("F1:K1").Resize(r).BorderAround ColorIndex:=23, Weight:=xlThick
End Sub

Thanks for your help. This has partly done what I need but has only put a thin border round F2:F and then thick around the whole thing. What I need it to look like is shown in the attached photo please.

Is this possible?

 

[Yongle]

Here you go

Sub Border()
    Dim r As Long, c As Long, cel As Range
    For c = 6 To 11
        r = WorksheetFunction.Max(r, Cells(Rows.Count, c).End(xlUp).Row)
    Next
'thin lines around all cells   
    For Each cel In Range("F1:K1").Resize(r)
        cel.BorderAround ColorIndex:=23, Weight:=xlThin
    Next
'thick border around the whole thing
    Range("F2:K" & r).BorderAround ColorIndex:=23, Weight:=xlThick
'thick border around row1
    Range("F1:K1").BorderAround ColorIndex:=23, Weight:=xlThick
'thin left border to column F
    For Each cel In Range("F2:F" & r)
        With cel.Borders(xlEdgeLeft)
            .ColorIndex = 23
            .Weight = xlThin
        End With
    Next
End Sub

 

[ChrisFoster]

Here you go

Sub Border()
    Dim r As Long, c As Long, cel As Range
    For c = 6 To 11
        r = WorksheetFunction.Max(r, Cells(Rows.Count, c).End(xlUp).Row)
    Next
'thin lines around all cells  
    For Each cel In Range("F1:K1").Resize(r)
        cel.BorderAround ColorIndex:=23, Weight:=xlThin
    Next
'thick border around the whole thing
    Range("F2:K" & r).BorderAround ColorIndex:=23, Weight:=xlThick
'thick border around row1
    Range("F1:K1").BorderAround ColorIndex:=23, Weight:=xlThick
'thin left border to column F
    For Each cel In Range("F2:F" & r)
        With cel.Borders(xlEdgeLeft)
            .ColorIndex = 23
            .Weight = xlThin
        End With
    Next
End Sub

This is so very close, the only issue is that the left of column F is a think line rather than a thick line (see attachment). Any ideas how to solve that?

P.S Thanks for helping me with this.

 

[Yongle]

the only issue is that the left of column F is a think line rather than a thick line

that sentence makes no sense

 

[Fluff]

Another option

Sub ChrisFoster()
    With Range("F1:K" & Range("F" & Rows.Count).End(xlUp).Row)
      .Borders.ColorIndex = 23
      .Borders.Weight = xlThin
      .BorderAround , xlThick, 23
      .Resize(1).Borders(xlEdgeBottom).Weight = xlThick
   End With
End Sub

 

[ChrisFoster]

that sentence makes no sense

Sorry, I meant thin line not think line.

Another option

Sub ChrisFoster()
    With Range("F1:K" & Range("F" & Rows.Count).End(xlUp).Row)
      .Borders.ColorIndex = 23
      .Borders.Weight = xlThin
      .BorderAround , xlThick, 23
      .Resize(1).Borders(xlEdgeBottom).Weight = xlThick
   End With
End Sub

This has worked perfectly, thank-you.

 

[Fluff]

Glad we could help & thanks for the feedback.