Automatic Borders
 

Hi,

I have 55 workbooks and all need to be formatted exactly the same, but have
different ammounts of data. The First line of data is at Row 10 and I need
a border every 4 rows after Row 10 until the end. The total number of rows
is not necessarily a multiple of 4, so at the data at the end may only be 3
rows, and the border needs to be after the last row. How can I accomplish
this?

Thanks,

Mike

10
-------- border
11
12
13
14
-------- border
15
16
17
18
-------- border
19
-------- border

Automatic Borders
 

i know no one here likes loops but i'm not smart enough to not use them


j = 10
startdo:
if cells(j,1).value = "" then
range(cells(j-1,1),cells(j-1,"put number of columns you want to border
here").borders(edgebottom).linestyle = xlcontinuous
exit sub
end if
k = j - 10
if k/4 < int(k/4) then goto skip
range(cells(j,1),cells(j,"put number of columns you want to border
here").borders(edgebottom).linestyle = xlcontinuous
skip:
j=j+1
goto startdo


"Reed" wrote:

Hi,

I have 55 workbooks and all need to be formatted exactly the same, but have
different ammounts of data. The First line of data is at Row 10 and I need
a border every 4 rows after Row 10 until the end. The total number of rows
is not necessarily a multiple of 4, so at the data at the end may only be 3
rows, and the border needs to be after the last row. How can I accomplish
this?

Thanks,

Mike

10
-------- border
11
12
13
14
-------- border
15
16
17
18
-------- border
19
-------- border

Automatic Borders
 

Reed, try something like this. You will need to modify the columns and
border attributes to suite your needs:

Option Explicit
Sub autoBorders()
Dim cntRows&, X&
cntRows = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For X = 10 To cntRows - 1 Step 4
'This example assumes the border
'is to be in columns A to D
'beginning in row 10.
Range(Cells(X, 1), Cells(X, 4)).Select
With Selection.Borders(xlEdgeBottom)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
Next X
Range(Cells(cntRows, 1), Cells(cntRows, 4)).Select
With Selection.Borders(xlEdgeBottom)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
End Sub

Regards--Lonnie M.

Automatic Borders
 

Worked Great. You are a Genius! Thanks!


"Lonnie M." wrote in message
oups.com...
Reed, try something like this. You will need to modify the columns and
border attributes to suite your needs:

Option Explicit
Sub autoBorders()
Dim cntRows&, X&
cntRows = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For X = 10 To cntRows - 1 Step 4
'This example assumes the border
'is to be in columns A to D
'beginning in row 10.
Range(Cells(X, 1), Cells(X, 4)).Select
With Selection.Borders(xlEdgeBottom)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
Next X
Range(Cells(cntRows, 1), Cells(cntRows, 4)).Select
With Selection.Borders(xlEdgeBottom)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
End Sub

Regards--Lonnie M.