I would like a macro to automatically put a border around any cells in
a sheet that has data in it (numbers or text)

and cells with no data have no borders.

thanks

Johnnboy

Johnnyboy5 submitted this idea :
I would like a macro to automatically put a border around any cells in
a sheet that has data in it (numbers or text)

and cells with no data have no borders.

thanks

Johnnboy

This is something I would normally use ConditionalFormatting for
because a macro would have to be fired by a sheet event, which could
seriously hurt performance if there's a lot of data. Excel, however,
can handle this much better (and more efficiently) without penalty.

To do this using CF...
1. Click the intersecting header of row/column headers at the top left
corner of the sheet you want borders placed.

2. Choose Conditional Formatting... from the Format menu.

3. Select 'Formula' from the dropdown.

4. Enter: =A1<"" in the criteria box.

5. click the Format button and configure your border properties.

6. click OK twice.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jan 22, 1:40*pm, GS wrote:
Johnnyboy5 submitted this idea :

I would like a macro to automatically put a border around any cells in
a sheet that has data in it (numbers or text)

and cells with no data have no borders.

thanks

Johnnboy

This is something I would normally use ConditionalFormatting for
because a macro would have to be fired by a sheet event, which could
seriously hurt performance if there's a lot of data. Excel, however,
can handle this much better (and more efficiently) without penalty.

To do this using CF...
1. *Click the intersecting header of row/column headers at the top left
corner of the sheet you want borders placed.

2. *Choose Conditional Formatting... from the Format menu.

3. *Select 'Formula' from the dropdown.

4. *Enter: *=A1<"" *in the criteria box.

5. *click the Format button and configure your border properties.

6. *click OK twice.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks - that's an awsume solution.

job done.

5 star answer

Sub BorderRange()
With ActiveSheet
la = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Address
..Range("a1:" & la).Borders.LineStyle = xlContinuous
End With
End Sub

On Jan 22, 7:25*am, Johnnyboy5 wrote:
I would like a macro to automatically put a border around any cells in
a sheet that has data in it (numbers or text)

and cells with no data have no borders.

thanks

Johnnboy

On Jan 22, 3:13*pm, Don Guillett Excel MVP
wrote:
Sub BorderRange()
With ActiveSheet
la = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Address
.Range("a1:" & la).Borders.LineStyle = xlContinuous
End With
End Sub

On Jan 22, 7:25*am, Johnnyboy5 wrote:

I would like a macro to automatically put a border around any cells in
a sheet that has data in it (numbers or text)

and cells with no data have no borders.

thanks

Johnnboy

Thanks Don for your reply -

Johnnyboy5 brought next idea :
Thanks - that's an awsume solution.

job done.

5 star answer

You're very welcome!
Thanks for the feedback, ..always appreciated!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc