I'm fairly new to VBA programming and using Excel 2003. I'm interested in
learning how to make a Macro that creates a number of thick borders across
columns "A" to "AA". These borders will divide lines of information from one
job number to the next, but the job numbers have a random number of
operations assigned to them, each with it's own row.
Such as: M1234 - Operation A <next row> M1234 -Operation B <next row>
M1234 - Operation C <next row> M1235 Operation A and so on. I am already
using some conditional formatting in a few of the columns of the worksheet,
so a macro seems my best option. The worksheet is about 1300 rows long, but
that is also random, so I'm looking to automatically adjust to the length.
Any tips will be appreciated. Thanks.

Sub Demo()

dim i as integer
with range("a2")
do until isempty(.offset(i,0))
if not .offset(i-1,0).value = .offset(i,0).value then
.offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
end if
i=i+1
loop
end with

End Sub

"MSchmidty2" wrote:

> I'm fairly new to VBA programming and using Excel 2003. I'm interested in
> learning how to make a Macro that creates a number of thick borders across
> columns "A" to "AA". These borders will divide lines of information from one
> job number to the next, but the job numbers have a random number of
> operations assigned to them, each with it's own row.
> Such as: M1234 - Operation A <next row> M1234 -Operation B <next row>
> M1234 - Operation C <next row> M1235 Operation A and so on. I am already
> using some conditional formatting in a few of the columns of the worksheet,
> so a macro seems my best option. The worksheet is about 1300 rows long, but
> that is also random, so I'm looking to automatically adjust to the length.
> Any tips will be appreciated. Thanks.

Not sure exactly what you have in mind by "across columns"... do you want
the range surrounded by a border all around or do you just want lines across
the bottom of the range? To surround the range with a border, you could do
this...

Range("A5:AA8").Cells.BorderAround Weight:=xlThick

although you should look up BorderAround in the help files as there are two
other optional arguments available and several options available for each
argument. If you just want to draw a horizontal line at the bottom of the
range (sort of as a separator between sections, you could do this...

Range("A5:AA8").Cells.Borders(xlEdgeBottom).Weight=xlThick

and, again, you should look up the Borders property to see the options
available to you for it.

Rick


"MSchmidty2" <(E-Mail Removed)> wrote in message
news:3F444F83-B56B-4949-A797-(E-Mail Removed)...
> I'm fairly new to VBA programming and using Excel 2003. I'm interested in
> learning how to make a Macro that creates a number of thick borders across
> columns "A" to "AA". These borders will divide lines of information from
> one
> job number to the next, but the job numbers have a random number of
> operations assigned to them, each with it's own row.
> Such as: M1234 - Operation A <next row> M1234 -Operation B <next row>
> M1234 - Operation C <next row> M1235 Operation A and so on. I am already
> using some conditional formatting in a few of the columns of the
> worksheet,
> so a macro seems my best option. The worksheet is about 1300 rows long,
> but
> that is also random, so I'm looking to automatically adjust to the length.
> Any tips will be appreciated. Thanks.

Sam, Thank you. this is what I was looking for. Two questions: 1. How can
I change the color of the line to dark red? And secondly, is there a way to
compensate for hidden rows? I'm using other control toolbox button macros to
display certain information within the same job number. Again, thanks.

"Sam Wilson" wrote:

> Sub Demo()
>
> dim i as integer
> with range("a2")
> do until isempty(.offset(i,0))
> if not .offset(i-1,0).value = .offset(i,0).value then
> .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
> end if
> i=i+1
> loop
> end with
>
> End Sub
>
> "MSchmidty2" wrote:
>
> > I'm fairly new to VBA programming and using Excel 2003. I'm interested in
> > learning how to make a Macro that creates a number of thick borders across
> > columns "A" to "AA". These borders will divide lines of information from one
> > job number to the next, but the job numbers have a random number of
> > operations assigned to them, each with it's own row.
> > Such as: M1234 - Operation A <next row> M1234 -Operation B <next row>
> > M1234 - Operation C <next row> M1235 Operation A and so on. I am already
> > using some conditional formatting in a few of the columns of the worksheet,
> > so a macro seems my best option. The worksheet is about 1300 rows long, but
> > that is also random, so I'm looking to automatically adjust to the length.
> > Any tips will be appreciated. Thanks.

Colour is easy - After the line:

..offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick

add this line
..offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Colorindex = 9


Not quite sure what you mean about the hidden rows?

"MSchmidty2" wrote:

> Sam, Thank you. this is what I was looking for. Two questions: 1. How can
> I change the color of the line to dark red? And secondly, is there a way to
> compensate for hidden rows? I'm using other control toolbox button macros to
> display certain information within the same job number. Again, thanks.
>
> "Sam Wilson" wrote:
>
> > Sub Demo()
> >
> > dim i as integer
> > with range("a2")
> > do until isempty(.offset(i,0))
> > if not .offset(i-1,0).value = .offset(i,0).value then
> > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
> > end if
> > i=i+1
> > loop
> > end with
> >
> > End Sub
> >
> > "MSchmidty2" wrote:
> >
> > > I'm fairly new to VBA programming and using Excel 2003. I'm interested in
> > > learning how to make a Macro that creates a number of thick borders across
> > > columns "A" to "AA". These borders will divide lines of information from one
> > > job number to the next, but the job numbers have a random number of
> > > operations assigned to them, each with it's own row.
> > > Such as: M1234 - Operation A <next row> M1234 -Operation B <next row>
> > > M1234 - Operation C <next row> M1235 Operation A and so on. I am already
> > > using some conditional formatting in a few of the columns of the worksheet,
> > > so a macro seems my best option. The worksheet is about 1300 rows long, but
> > > that is also random, so I'm looking to automatically adjust to the length.
> > > Any tips will be appreciated. Thanks.

When the macro you provided has been run and rows are then hidden, the thick
borders are hidden with them if they fall in the first or last row of a job
number. I'm wondering if a macro can 'ignore' hidden rows and apply only to
the rows on screen. I appreciate your assistance.

"Sam Wilson" wrote:

> Colour is easy - After the line:
>
> .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
>
> add this line
> .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Colorindex = 9
>
>
> Not quite sure what you mean about the hidden rows?
>
> "MSchmidty2" wrote:
>
> > Sam, Thank you. this is what I was looking for. Two questions: 1. How can
> > I change the color of the line to dark red? And secondly, is there a way to
> > compensate for hidden rows? I'm using other control toolbox button macros to
> > display certain information within the same job number. Again, thanks.
> >
> > "Sam Wilson" wrote:
> >
> > > Sub Demo()
> > >
> > > dim i as integer
> > > with range("a2")
> > > do until isempty(.offset(i,0))
> > > if not .offset(i-1,0).value = .offset(i,0).value then
> > > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
> > > end if
> > > i=i+1
> > > loop
> > > end with
> > >
> > > End Sub
> > >
> > > "MSchmidty2" wrote:
> > >
> > > > I'm fairly new to VBA programming and using Excel 2003. I'm interested in
> > > > learning how to make a Macro that creates a number of thick borders across
> > > > columns "A" to "AA". These borders will divide lines of information from one
> > > > job number to the next, but the job numbers have a random number of
> > > > operations assigned to them, each with it's own row.
> > > > Such as: M1234 - Operation A <next row> M1234 -Operation B <next row>
> > > > M1234 - Operation C <next row> M1235 Operation A and so on. I am already
> > > > using some conditional formatting in a few of the columns of the worksheet,
> > > > so a macro seems my best option. The worksheet is about 1300 rows long, but
> > > > that is also random, so I'm looking to automatically adjust to the length.
> > > > Any tips will be appreciated. Thanks.

.offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
.offset(i-1,0).Resize(1, 27).Borders(xlEdgebottom).Weight = xlThick
.offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Colorindex = 9
.offset(i-1,0).Resize(1, 27).Borders(xlEdgebottom).Colorindex = 9

This will colour the top of the second row and the bottom of the top row, so
if either is visible it will show up.

Sam

"MSchmidty2" wrote:

> When the macro you provided has been run and rows are then hidden, the thick
> borders are hidden with them if they fall in the first or last row of a job
> number. I'm wondering if a macro can 'ignore' hidden rows and apply only to
> the rows on screen. I appreciate your assistance.
>
> "Sam Wilson" wrote:
>
> > Colour is easy - After the line:
> >
> > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
> >
> > add this line
> > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Colorindex = 9
> >
> >
> > Not quite sure what you mean about the hidden rows?
> >
> > "MSchmidty2" wrote:
> >
> > > Sam, Thank you. this is what I was looking for. Two questions: 1. How can
> > > I change the color of the line to dark red? And secondly, is there a way to
> > > compensate for hidden rows? I'm using other control toolbox button macros to
> > > display certain information within the same job number. Again, thanks.
> > >
> > > "Sam Wilson" wrote:
> > >
> > > > Sub Demo()
> > > >
> > > > dim i as integer
> > > > with range("a2")
> > > > do until isempty(.offset(i,0))
> > > > if not .offset(i-1,0).value = .offset(i,0).value then
> > > > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
> > > > end if
> > > > i=i+1
> > > > loop
> > > > end with
> > > >
> > > > End Sub
> > > >
> > > > "MSchmidty2" wrote:
> > > >
> > > > > I'm fairly new to VBA programming and using Excel 2003. I'm interested in
> > > > > learning how to make a Macro that creates a number of thick borders across
> > > > > columns "A" to "AA". These borders will divide lines of information from one
> > > > > job number to the next, but the job numbers have a random number of
> > > > > operations assigned to them, each with it's own row.
> > > > > Such as: M1234 - Operation A <next row> M1234 -Operation B <next row>
> > > > > M1234 - Operation C <next row> M1235 Operation A and so on. I am already
> > > > > using some conditional formatting in a few of the columns of the worksheet,
> > > > > so a macro seems my best option. The worksheet is about 1300 rows long, but
> > > > > that is also random, so I'm looking to automatically adjust to the length.
> > > > > Any tips will be appreciated. Thanks.

Thank you, Sam. It works great!

"Sam Wilson" wrote:

> .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
> .offset(i-1,0).Resize(1, 27).Borders(xlEdgebottom).Weight = xlThick
> .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Colorindex = 9
> .offset(i-1,0).Resize(1, 27).Borders(xlEdgebottom).Colorindex = 9
>
> This will colour the top of the second row and the bottom of the top row, so
> if either is visible it will show up.
>
> Sam
>
> "MSchmidty2" wrote:
>
> > When the macro you provided has been run and rows are then hidden, the thick
> > borders are hidden with them if they fall in the first or last row of a job
> > number. I'm wondering if a macro can 'ignore' hidden rows and apply only to
> > the rows on screen. I appreciate your assistance.
> >
> > "Sam Wilson" wrote:
> >
> > > Colour is easy - After the line:
> > >
> > > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
> > >
> > > add this line
> > > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Colorindex = 9
> > >
> > >
> > > Not quite sure what you mean about the hidden rows?
> > >
> > > "MSchmidty2" wrote:
> > >
> > > > Sam, Thank you. this is what I was looking for. Two questions: 1. How can
> > > > I change the color of the line to dark red? And secondly, is there a way to
> > > > compensate for hidden rows? I'm using other control toolbox button macros to
> > > > display certain information within the same job number. Again, thanks.
> > > >
> > > > "Sam Wilson" wrote:
> > > >
> > > > > Sub Demo()
> > > > >
> > > > > dim i as integer
> > > > > with range("a2")
> > > > > do until isempty(.offset(i,0))
> > > > > if not .offset(i-1,0).value = .offset(i,0).value then
> > > > > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
> > > > > end if
> > > > > i=i+1
> > > > > loop
> > > > > end with
> > > > >
> > > > > End Sub
> > > > >
> > > > > "MSchmidty2" wrote:
> > > > >
> > > > > > I'm fairly new to VBA programming and using Excel 2003. I'm interested in
> > > > > > learning how to make a Macro that creates a number of thick borders across
> > > > > > columns "A" to "AA". These borders will divide lines of information from one
> > > > > > job number to the next, but the job numbers have a random number of
> > > > > > operations assigned to them, each with it's own row.
> > > > > > Such as: M1234 - Operation A <next row> M1234 -Operation B <next row>
> > > > > > M1234 - Operation C <next row> M1235 Operation A and so on. I am already
> > > > > > using some conditional formatting in a few of the columns of the worksheet,
> > > > > > so a macro seems my best option. The worksheet is about 1300 rows long, but
> > > > > > that is also random, so I'm looking to automatically adjust to the length.
> > > > > > Any tips will be appreciated. Thanks.

I am also interested in making sure all of the cells on the worksheet with
information in them have thin borders by using a click button macro:

Private Sub CommandButton5_Click()
Set Range11 = ActiveSheet.Range(Range("A3"), Range("AA65536").End(xlUp))
Range11.Borders.Weight = xlThin
End Sub

The macro only changes borders until row 1092. I'm guessing there is a
limit, but I can't figure out how to get around it. Any tips?

"MSchmidty2" wrote:

> Thank you, Sam. It works great!
>
> "Sam Wilson" wrote:
>
> > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
> > .offset(i-1,0).Resize(1, 27).Borders(xlEdgebottom).Weight = xlThick
> > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Colorindex = 9
> > .offset(i-1,0).Resize(1, 27).Borders(xlEdgebottom).Colorindex = 9
> >
> > This will colour the top of the second row and the bottom of the top row, so
> > if either is visible it will show up.
> >
> > Sam
> >
> > "MSchmidty2" wrote:
> >
> > > When the macro you provided has been run and rows are then hidden, the thick
> > > borders are hidden with them if they fall in the first or last row of a job
> > > number. I'm wondering if a macro can 'ignore' hidden rows and apply only to
> > > the rows on screen. I appreciate your assistance.
> > >
> > > "Sam Wilson" wrote:
> > >
> > > > Colour is easy - After the line:
> > > >
> > > > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
> > > >
> > > > add this line
> > > > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Colorindex = 9
> > > >
> > > >
> > > > Not quite sure what you mean about the hidden rows?
> > > >
> > > > "MSchmidty2" wrote:
> > > >
> > > > > Sam, Thank you. this is what I was looking for. Two questions: 1. How can
> > > > > I change the color of the line to dark red? And secondly, is there a way to
> > > > > compensate for hidden rows? I'm using other control toolbox button macros to
> > > > > display certain information within the same job number. Again, thanks.
> > > > >
> > > > > "Sam Wilson" wrote:
> > > > >
> > > > > > Sub Demo()
> > > > > >
> > > > > > dim i as integer
> > > > > > with range("a2")
> > > > > > do until isempty(.offset(i,0))
> > > > > > if not .offset(i-1,0).value = .offset(i,0).value then
> > > > > > .offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
> > > > > > end if
> > > > > > i=i+1
> > > > > > loop
> > > > > > end with
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > "MSchmidty2" wrote:
> > > > > >
> > > > > > > I'm fairly new to VBA programming and using Excel 2003. I'm interested in
> > > > > > > learning how to make a Macro that creates a number of thick borders across
> > > > > > > columns "A" to "AA". These borders will divide lines of information from one
> > > > > > > job number to the next, but the job numbers have a random number of
> > > > > > > operations assigned to them, each with it's own row.
> > > > > > > Such as: M1234 - Operation A <next row> M1234 -Operation B <next row>
> > > > > > > M1234 - Operation C <next row> M1235 Operation A and so on. I am already
> > > > > > > using some conditional formatting in a few of the columns of the worksheet,
> > > > > > > so a macro seems my best option. The worksheet is about 1300 rows long, but
> > > > > > > that is also random, so I'm looking to automatically adjust to the length.
> > > > > > > Any tips will be appreciated. Thanks.

I'll bet the reason it stops at Row 1092 is because the last piece of data
in Column AA is located at Row 1092. You put the End(xlUP) evaluation on the
last cell in Column AA, so that where it will look up from, no matter how
the rest of your data is distributed. Try this macro (it uses a different
approach) and see if it works for you...

Private Sub CommandButton5_Click()
With Worksheets("Sheet1").UsedRange
.SpecialCells(xlConstants).Borders.Weight = xlThin
.SpecialCells(xlCellTypeFormulas).Borders.Weight = xlThin
End With
End Sub

Rick


>I am also interested in making sure all of the cells on the worksheet with
> information in them have thin borders by using a click button macro:
>
> Private Sub CommandButton5_Click()
> Set Range11 = ActiveSheet.Range(Range("A3"), Range("AA65536").End(xlUp))
> Range11.Borders.Weight = xlThin
> End Sub
>
> The macro only changes borders until row 1092. I'm guessing there is a
> limit, but I can't figure out how to get around it. Any tips?