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



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
.



Relevant Pages

  • RE: Do Loop and data formatting question
    ... You shouldn't have to select the cells. ... End Sub ... cell in column "B" based on the level that the item report to. ... trying to do a conditional do loop but I am get error with the ...
    (microsoft.public.excel.programming)
  • News: Cells "from space" have unusual makeup
    ... Report: cells "from space" have unusual makeup ... Louis and Santhosh Kumar of Mahatma Gandhi University in India, ... tiny red globules fell to Earth in a red rain that pelted parts ...
    (sci.bio.evolution)
  • Re: Removing Rows for Printing
    ... One is to clear a sheet of all input cells leaving ... .PrintOut ' for testing use .PrintPreview ... I attached the script to the Print Button. ... I hid the Report worksheet. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: I need help in writing/modifying an EXCEL macro
    ... Every month I download a report from a state agency that I need to reformat, ... Headings -contain fixed headings in merged & centered cells. ... Row 7 - Start of reporting period information in cell A7 and merged cells ...
    (microsoft.public.excel)
  • Re: GridPack V2.0 for ReportBuilder - now better than ever !!!
    ... > to work with ReportBuilder, the report tool from Digital Metaphors, ... > TrbTableGrid is a stretchable table grid, ... > - Cells can be merged and split. ... > - All the features of TrbTableGrid, plus, ...
    (borland.public.delphi.thirdpartytools.general)