Borders with VBA

RichM

New Member
Joined
Aug 6, 2002
Messages
11
Folks, I am in need of your assistance once again. I am working on a reporting format that requires cells with data to have (thin box) borders around it. I have tried various routes that work if the range is known. However, the amount of data various. One week it may be 100 rows, the next 168 or even 24. Is there a VBA script that can handle this?

Thank you for your assistance.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If your data always starts in the same place and is contiguous it's easy:

Range("A1").CurrentRegion.Select

selects down and across until a blank cell is reached.
 

RichM

New Member
Joined
Aug 6, 2002
Messages
11
Thank you both for your response.
Richie - I am not too sure how to create a named range for an unknown.
Andrew - I will try this. What can I do for blank cells. They don't need borders.

Thank you again.
Rich
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Once you have your range you can loop around it like this:

Code:
Sub Test()
   Dim Rng As Range
   Dim c As Range
   Set Rng = Activecell.CurrentRegion
   For Each c in Rng
'     Test if cell is blank
      If c.Value <> "" Then
'     *** Do your borders stuff ***
      End If
   Next c
End Sub
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
This may work, without seeing your sheet?
Test it on a copy.
The code go's in the Sheet Module for the sheet you want it to work on. JSW

Sub myOutline(CurrRange As Range)
If Cell.Value < 0 Then
With Cell.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Cell.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Cell.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Cell.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next Cell
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,088
Members
412,310
Latest member
mark884
Top