loop until empty row and column problem (format cells until the lastrow and column in a report)

[Brent]

i have a report that is imported into a worksheet from external data
in a database.

I am using Macros to format and filter the report.

The number of rows in the report can change each time it is refreshed.
I would like to add borders to all cells in the report but I do not
want the macro to continue adding borders once it gets to the last row
or column in the report.

There can be the odd random blank cell in the report but and I would
rather these cells are not skipped and still formatted with a border.

I have only come up with a way to keep adding borders down a column
untill it comes across 1 empty cell and then offsets to the next
column and starts again. This does not work if it comes accross a
blank cell halfway down the column. I wonder if I can run this loop
until it comes across 5 empty cells in succession in the column.

If you use the macro below on a sheet with 6 columns and 100 rows of
dummy data you will see how it works. Im sure there is a far better
way?

Sub borderallnonblankcells()
Dim Resp As VbMsgBoxResult
Resp = MsgBox(prompt:=" Do you want cell borders to the Rport?",
Buttons:=vbYesNo)
If Resp = vbYes Then
' code if user clicked 'Yes'
Application.ScreenUpdating = False
oldSB = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "GENERATING REPORT PLEASE WAIT"
Application.ScreenUpdating = False
'Code
Range("A3").Activate
Do Until ActiveCell.Value = ""
Selection.Borders.Value = 1
ActiveCell.Offset(1, 0).Select
Loop
'HOME OFSET col 1 AND LOOP AGAIN
Range("A3").Activate
ActiveCell.Offset(0, 1).Select
Do Until ActiveCell.Value = ""
Selection.Borders.Value = 1
ActiveCell.Offset(1, 0).Select
Loop
'HOME OFSET col 2 AND LOOP AGAIN
Range("A3").Activate
ActiveCell.Offset(0, 2).Select
Do Until ActiveCell.Value = ""
Selection.Borders.Value = 1
ActiveCell.Offset(1, 0).Select
Loop
'HOME OFSET col 3 AND LOOP AGAIN
Range("A3").Activate
ActiveCell.Offset(0, 3).Select
Do Until ActiveCell.Value = ""
Selection.Borders.Value = 1
ActiveCell.Offset(1, 0).Select
Loop
'HOME OFFSET col 4 AND LOOP AGAIN
Range("A3").Activate
ActiveCell.Offset(0, 4).Select
Do Until ActiveCell.Value = ""
Selection.Borders.Value = 1
ActiveCell.Offset(1, 0).Select
Loop
'HOME OFFSET col 5 AND LOOP AGAIN
Range("A3").Activate
ActiveCell.Offset(0, 5).Select
Do Until ActiveCell.Value = ""
Selection.Borders.Value = 1
ActiveCell.Offset(1, 0).Select
Loop
'HOME OFFSET col 5 AND LOOP AGAIN
Range("A3").Activate
ActiveCell.Offset(0, 6).Select
Do Until ActiveCell.Value = ""
Selection.Borders.Value = 1
ActiveCell.Offset(1, 0).Select
Loop
Application.StatusBar = False
Application.DisplayStatusBar = oldSB
Application.ScreenUpdating = True
Else
Range("A1").Activate
End If

End Sub

 

[Ron Rosenfeld]

I have only come up with a way to keep adding borders down a column
untill it comes across 1 empty cell and then offsets to the next
column and starts again. This does not work if it comes accross a
blank cell halfway down the column. I wonder if I can run this loop
until it comes across 5 empty cells in succession in the column.

If you use the macro below on a sheet with 6 columns and 100 rows of
dummy data you will see how it works. Im sure there is a far better
way?

You can, much more quickly, determine the range to be formatted, and then format it in one step.
Although there can be some problems with Excel keeping track of the last cell, if you are continually adding data, and that's all on that is on the sheet, you could do something like:

Cells.Borders.Value = 0
Set r = ActiveSheet.UsedRange
r.Borders.Value = 1

In other words, operate just on UsedRange.

If there is other data on your sheet, and you want to restrict your formatting to just the used range in columns A:F that contain data, then try something like:

=====================
Option Explicit
Sub FormatStuff()
Dim r As Range
Dim LastRow As Long, LastCol As Long
Dim i As Long

Cells.Borders.Value = 0
With Range("A1", Cells(Rows.Count, "F"))
LastRow = .Find(what:="*", after:=[a1], _
LookIn:=xlValues, _
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
LastCol = .Find(what:="*", after:=[a1], _
LookIn:=xlValues, _
lookat:=xlPart, searchorder:=xlByColumns, _
searchdirection:=xlPrevious).Column
End With

Set r = Range("A1", Cells(LastRow, LastCol))
r.Borders.Value = 1
End Sub
===========================

If your range starts somewhere other than A1, make appropriate changes.
Obviously you can extend this method to other operations on this range. Once you have it working, you can turn off screen updating to increase the speed.

 

[Michael Bednarek]

i have a report that is imported into a worksheet from external data
in a database.

I am using Macros to format and filter the report.

The number of rows in the report can change each time it is refreshed.
I would like to add borders to all cells in the report but I do not
want the macro to continue adding borders once it gets to the last row
or column in the report.

There can be the odd random blank cell in the report but and I would
rather these cells are not skipped and still formatted with a border.

[snip]

The following code will place borders around all cells falling between
"A1" and the intersection of the last row in column A and the last
column in row 1. The last row/column is determined by first going to
the bottom/right-most row/column and then going up/left to the first
non-blank row/column.

Range(LastRow(Range("A1")), LastCol(Range("A1"))).Borders.Value = 1

==========
Function LastRow(rngStartCell As Range) As Range

With rngStartCell
Set LastRow = .Parent.Cells(.Parent.Rows.Count, .Column).End(xlUp)
If Len(LastRow.Value) = 0 Then Set LastRow = rngStartCell
End With
End Function
==========
Function LastCol(rngStartCell As Range) As Range

With rngStartCell
Set LastCol = .Parent.Cells(.Row, .Parent.Columns.Count).End(xlToLeft)
If Len(LastCol.Value) = 0 Then Set LastCol = rngStartCell
End With
End Function
==========

 

[Brent]

I have only come up with a way to keep adding borders down a column
untill it comes across 1 empty cell and then offsets to the next
column and starts again. This does not work if it comes accross a
blank cell halfway down the column. I wonder if I can run this loop
until it comes across 5 empty cells in succession in the column.

If you use the macro below on a sheet with 6 columns and 100 rows of
dummy data you will see how it works. Im sure there is a far better
way?

You can, much more quickly, determine the range to be formatted, and thenformat it in one step.
Although there can be some problems with Excel keeping track of the last cell, if you are continually adding data, and that's all on that is on the sheet, you could do something like:

Cells.Borders.Value = 0
Set r = ActiveSheet.UsedRange
r.Borders.Value = 1

In other words, operate just on UsedRange.

If there is other data on your sheet, and you want to restrict your formatting to just the used range in columns A:F that contain data, then try something like:

=====================
Option Explicit
Sub FormatStuff()
    Dim r As Range
Dim LastRow As Long, LastCol As Long
Dim i As Long

Cells.Borders.Value = 0
With Range("A1", Cells(Rows.Count, "F"))
    LastRow = .Find(what:="*", after:=[a1], _
        LookIn:=xlValues, _
        lookat:=xlPart, searchorder:=xlByRows, _
        searchdirection:=xlPrevious).Row
    LastCol = .Find(what:="*", after:=[a1], _
        LookIn:=xlValues, _
        lookat:=xlPart, searchorder:=xlByColumns, _
        searchdirection:=xlPrevious).Column
End With

Set r = Range("A1", Cells(LastRow, LastCol))
r.Borders.Value = 1
End Sub
===========================

If your range starts somewhere other than A1, make appropriate changes.
Obviously you can extend this method to other operations on this range.  Once you have it working, you can turn off screen updating to increase the speed.

This is Perfect, and very well explained.
As you say this could be used for many purposes.

Thankyou!!

 

[Ron Rosenfeld]

This is Perfect, and very well explained.
As you say this could be used for many purposes.

Thankyou!!

Glad to help. Thanks for the feedback.