PDA

View Full Version : Solved: extend the border line



Yjmmay34
06-14-2010, 05:47 PM
Hello, all.
I need a macro to extend border line refer to the previous . The previous border line are ended at column G. I want to extend it to column AA. Is that possible to be done using a macro? Because i got numerical border lines. And here is an example. Please see the attachment.
Thank you!

slamet Harto
06-15-2010, 01:02 AM
i can't see your vba code

Bob Phillips
06-15-2010, 01:02 AM
With Range("G1:G43")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
End With
With Range("H3:AA3")
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
End With
With Range("H10:AA10")
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
End With
With Range("H12:AA12")
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
End With
With Range("H18:AA18")
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
End With
With Range("H22:AA22")
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
End With
With Range("AA1:AA23")
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End With

Yjmmay34
06-15-2010, 02:30 AM
hi,xld. Thanks for your attention. But cannot do like this, because i have not only these rows. there are thousand of border lines need to extend and many sheets. So i hope there is more automatic way to solve this Q. Thank you.

Bob Phillips
06-15-2010, 03:31 AM
Maybe this will work for you



Dim cell As Range
Dim NumRows As Long
Dim i As Long

NumRows = ActiveSheet.UsedRange.Rows.Count

With Columns(7)

.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
End With

For i = 1 To NumRows

If Cells(i, "A").Borders(xlEdgeBottom).LineStyle <> xlNone Then

With Cells(i, "A").Resize(, 27).Borders(xlEdgeBottom)

.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
End If
Next i

With Range("AA1").Resize(NumRows)
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End With

Yjmmay34
06-15-2010, 06:51 PM
Hello, xld. Thanks a lot for your help. the macro is working perfectly. Thank you!
Have a nice day!