box all columns and rows

[Trevor3007]

hi,

does anyone have the vb to only box col\rows that contain text?

at present i use:-

Sub boarder()'
' boarder Macro
'


'
    Range("A1:F2663").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
End Sub

I am going to be using various user made excel sheets & they will be all different formats etc.

MTIA
KR
Trevor3007

 

[Logit]

.

Option Explicit


Sub boarder() '
' boarder Macro
'
Dim c As Range
'
    Range("A1:F2663").Select '<-- edit range as required
    'Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    'Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    For Each c In Selection
        If c.Value <> "" Then
            With c.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThick
            End With
            With c.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThick
            End With
            With c.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThick
            End With
            With c.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThick
            End With
            With c.Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThick
            End With
            With c.Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThick
            End With
        End If
    Next
    Range("A1").Select
End Sub

 

[MARK858]

only box col\rows that contain text

Maybe if it is just text (and not the result of formulas)...

Sub BORDERIT()
    With Range("A1:F2663")
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders.LineStyle = xlNone
        .SpecialCells(2, 2).Borders.Weight = xlThick
    End With
End Sub

 

[Trevor3007]

hi logit,

hope this finds you well & thanks for your reply.

Unfortunately your reply does not work , perhaps I have not explained correctly?

the code i sent is OK for my workbooks, but I will be getting worksheets from other and theirs wont be in my format.

therefore the data will differ.

there maybe on data in a1-e3 another may have data in a2-34 & from h7-p100 etc.

So I only want the box where there is data...

sorry for any confusion & MTIA
Trevor3007

 

[Trevor3007]

Thanks Mark858,

hope this finds you well & thanks for your reply.

Unfortunately your reply does not work , perhaps I have not explained correctly?

the code i sent is OK for my workbooks, but I will be getting worksheets from other and theirs wont be in my format.

therefore the data will differ.

there maybe on data in a1-e3 another may have data in a2-34 & from h7-p100 etc.

So I only want the box where there is data...

sorry for any confusion & MTIA
Trevor3007

 

[MARK858]

So I only want the box where there is data...

So how is the code I posted putting borders on cells with no data in them?

 

[Trevor3007]

So how is the code I posted putting borders on cells with no data in them?

hi mark,

thanks for your reply.

yes you are correct. it seems again I have not explained correctly....

Soif the 1st cell is b2 with data & the last cell with date is z21, would need a boxed area from b2-z21

so all that are would be boxed and not just b2 & z21

hope this is all you need

KR
Trevor3007

 

[Michael M]

Maybe this then....

Sub MM1()
ActiveSheet.UsedRange.Borders.Weight = xlThick
End Sub

 

[Rick Rothstein]

So if the 1st cell is b2 with data & the last cell with date is z21, would need a boxed area from b2-z21

For your example range B2:Z21, did you want a border around just the outside of that range or did you want each individual cell within it to have a border? If the latter, can there be blank cells within the range and, if so, should those blank cells get their own border or not?

 

[Trevor3007]

Maybe this then....

Sub MM1()
ActiveSheet.UsedRange.Borders.Weight = xlThick
End Sub

fantastic.... works a treat.

thank you & hope you have a great Xmas.
KR Trevor3007