need help with formatting data collected from database...

[mandles1]

Hi,
I couldn't get the search tool to work on this forum, so hopefully I'm
not repeating a previously asked question...
This is probably an easy solution to solve, but I have had very limited
experience working with VBA and excel macros.
At the moment, I have a macro that gathers data from a database and
then displays it on a worksheet. The macro also formats a border around
the data. At the moment, the code looks a bit like this:

With Range("A4:AB1000")
With .Borders(xlEdgeLeft)
..LineStyle = xlContinuous
..Weight = xlMedium
..ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
..LineStyle = xlContinuous
..Weight = xlMedium
..ColorIndex = xlAutomatic
End With
End With

and so on for some more border types. What I would like to do, would be
rather than have the line "With Range("A4:AB1000"), I'd like to replace
"AB1000" with a variable that accounts for the amount of rows of data
that is pulled out of the database, as the database is constantly
changing, and I suspect I will need much more than 1000 rows to be
formatted in the future.

Hope someone can help !
Thanks,
Mandy

 

[Steve]

There are a few way to do this. Try the sample spreadsheet ref # 69 here
http://www.xl-logic.com/pages/vba.html

Alternatively, create a dynamic named range and select this. If you decide
to use this, search for "dynamic named range" on the web for examples.

 

[Tim Williams]

You could try

Range("A4").CurrentRegion

if your block of data is contiguous and has empty rows/columns surrounding
it. Also you might think about being more explicit when you define the
range:

Eg:
ActiveSheet.Range("A4").CurrentRegion

This will avoid any future confusion as your code mutates and you add new
functionality...
Tim.