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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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
 

Forum statistics

Threads
1,148,034
Messages
5,744,441
Members
423,872
Latest member
ConorN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top