Simplify macro to show grid lines
I recorded a macro to add grid lines to a range of cells, and then I
converted the macro to a more readable format: Public Sub ShowGridLines(rng As Range) With rng .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone With .Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With .Borders(xlEdgeTop)... However, I thought there may be a way to simplify it even more, using the Borders collection: Dim b as Border For Each b in rng.Borders b.LineStyle = xlContinuous b.Weight = xlThin b.ColorIndex = xlAutomatic Next This would reduce all that code to six lines. The problem is this code adds every border, including xlDiagonalDown and xlDiagonalUp. Does anyone know how to filter these two borders from the collection? The recorded macro explicity sets these line styles to "xlNone" (see above). |
Simplify macro to show grid lines
Possibly
Sub efg() Dim b As Border i = 0 Set rng = ActiveCell For Each b In rng.Borders i = i + 1 If i < 5 And i < 6 Then b.LineStyle = xlContinuous b.Weight = xlThin b.ColorIndex = xlAutomatic End If Next End Sub -- Regards, Tom Ogilvy "Mike K" wrote in message ups.com... I recorded a macro to add grid lines to a range of cells, and then I converted the macro to a more readable format: Public Sub ShowGridLines(rng As Range) With rng .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone With .Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With .Borders(xlEdgeTop)... However, I thought there may be a way to simplify it even more, using the Borders collection: Dim b as Border For Each b in rng.Borders b.LineStyle = xlContinuous b.Weight = xlThin b.ColorIndex = xlAutomatic Next This would reduce all that code to six lines. The problem is this code adds every border, including xlDiagonalDown and xlDiagonalUp. Does anyone know how to filter these two borders from the collection? The recorded macro explicity sets these line styles to "xlNone" (see above). |
All times are GMT +1. The time now is 12:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com