Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
borders | Excel Discussion (Misc queries) | |||
Borders | Excel Discussion (Misc queries) | |||
Borders | Excel Worksheet Functions | |||
borders | Excel Discussion (Misc queries) | |||
Borders | Excel Programming |