need help with formatting data collected from database...
 

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


--
mandles1
------------------------------------------------------------------------
mandles1's Profile: http://www.excelforum.com/member.php...o&userid=29754
View this thread: http://www.excelforum.com/showthread...hreadid=494761

need help with formatting data collected from database...
 

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.

--
Steve


"mandles1" wrote in
message ...

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


--
mandles1
------------------------------------------------------------------------
mandles1's Profile:
http://www.excelforum.com/member.php...o&userid=29754
View this thread: http://www.excelforum.com/showthread...hreadid=494761

need help with formatting data collected from database...
 

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.

--
Tim Williams
Palo Alto, CA


"mandles1" wrote in
message ...

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


--
mandles1
------------------------------------------------------------------------
mandles1's Profile:
http://www.excelforum.com/member.php...o&userid=29754
View this thread: http://www.excelforum.com/showthread...hreadid=494761