A MACRO TO BUILD BORDERS

[lar48ry]

Hi - I am a new member of this news group and also new to VBA for
Excel.
I have some programming background that is rather dated (fortran 77,
Basic, and a specialty language called DAL (which, as far as I know is
no longer in existence)). I have procured a few books and surfed the
Excel help sites and have made some headway however, I find that I need
some help with a macro that I am developing.

The macro is intended to create borders around a selected range of
cells. I started by recording a macro to accomplish this. With a
little work I got this to work when I selected a cell in Column A. Now
I want to expand it to create the same borders by selecting the cells
in Column A that has a numerical value. It can do this either
individually or by activating all the cells in Column A that meets the
requirement and doing them at one time. The other criteria for the
macro is to ignore those rows that have already received their borders
(this part I have not even tackled yet, primarily due to the fact that
I can't get the first part to work yet). I have included a copy of
the macro for your perusal and hopefully comments.

In the macro below I receive a 'Run time error' 424 stating that an
Object is required for the line Lastrow =. I don't understand what
Object it is looking for.

Here's hoping that help is on the way!!! And Thank You in advance.


Sub MULTIBORDERS()
' MULTIBORDERS Macro
' Creates multiple borders based on a number being
' entered into column A
'
'

Dim Lastrow As Long
Dim Row_Index As Long
Dim RW As Integer

'Max number of rows
RW = 395
With ActiveSheet
'Search for the last row with data in Column A
Lastrow = Activate.Cells(Rows.Count, "1").End(xlUp).Row
For Row_Index = RW + 5 To Lastrow Step RW
Next
End With
'ActiveCell.Range("A1:I1").Select

'Create a set of borders for each line of the form that has
'information
Selection.BORDERS(xlDiagonalDown).LineStyle = xlNone
Selection.BORDERS(xlDiagonalUp).LineStyle = xlNone
With Selection.BORDERS(xlEdgeLeft)
..LineStyle = xlContinuous
..Weight = xlThick
..ColorIndex = xlAutomatic
End With
With Selection.BORDERS(xlEdgeTop)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
With Selection.BORDERS(xlEdgeBottom)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
With Selection.BORDERS(xlEdgeRight)
..LineStyle = xlContinuous
..Weight = xlThick
..ColorIndex = xlAutomatic
End With
With Selection.BORDERS(xlInsideVertical)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
Exit Sub
End Sub

 

[Myrna Larson]

Check out the SpecialCells method of the Range object for information on how
to select cells containing numbers.

 

[Norman Jones]

Hi Lar48ry,

In the macro below I receive a 'Run time error' 424 stating that an
Object is required for the line Lastrow =. I don't understand what
Object it is looking for. [cut]
Lastrow = Activate.Cells(Rows.Count, "1").End(xlUp).Row

There are two problems with this line:
(1) the initial Activate is not required and is erroneous,
(2) the column reference should either be the numeric 1 without quotes or
the alpha A with the quotes.

In any event the following seems to do what you want:

Sub MULTIBORDERS()
' MULTIBORDERS Macro
' Creates multiple borders based on a number being
' entered into column A
'
Dim rng As Range
Dim cell As Range

On Error GoTo XIT
Set rng = ActiveSheet.Columns("A").Cells
Set rng = rng.SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0

For Each cell In rng.Cells
With cell.Resize(1, 9)
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
Next cell
Exit Sub

XIT:
MsgBox "No numeric vales found in column A"

End Sub

I have assumed that you wish to apply borders to the cells in columns A:I
for each row that has a numeric constant in column A.

If the number of columns should be diiferent, simply adjust the line:

With cell.Resize(1, 9)

to suit, column 9 being column I.

If the numeric values may be the result of formulae, post back, if you need
to, for a revised version.

 

[Don Guillett]

try this one line (broken with continuation character)

Sub bordernumbers()
Columns(1).SpecialCells(xlConstants, xlNumbers) _
..Borders.LineStyle = xlContinuous
End Sub

 

[lar48ry]

Thanks Norman, works like a charm. Appreciate the time and effort.

 

[Don Guillett]

to do 9 columns and set to thick. The WITH line is ONE line.

Sub bordernumbers()
X = Cells(Rows.Count, 1).End(xlUp).row
With Range(Cells(1, 1), Cells(X, 9)).SpecialCells(xlConstants,
xlNumbers).Borders
..LineStyle = xlContinuous
..Weight = xlThick
End With
End Sub

 

[lar48ry]

Thanks Don, that also does the job. I am using your previous
suggestion in some print routines as a sort function. Was losing some
hair until that came up, thx a lot for that.

Learning all of the various potential commands is rather intimidating
not to mention frustrating. It is people like you that really help
bring this programming down to a manageable level.

Is there a book that explains just the commands, I have 3 of J.
Whalenbach's books, but am looking for one that is more like a
dictionary than how to.