I want to format some cells in a spreadsheet using VBA from MS Access. The
formatting code I have is working fine (thanks to Bernie Deitrick) but the
range of cells populated with data will change over time. What code do I
need to detect the range of populated cells (there will never be gaps in the
data)?

Many thanks.

Keith.

Use the CurrentRegion property of the range, eg

Dim myRange as Range
set myRange = Range("A1").CurrentRegion
myRange.Font.Size=12

assuming A1 is in the set of populated cells.

Cheers

On Jul 3, 9:48 pm, "Keith Wilby" wrote:
I want to format some cells in a spreadsheet using VBA from MS Access. The
formatting code I have is working fine (thanks to Bernie Deitrick) but the
range of cells populated with data will change over time. What code do I
need to detect the range of populated cells (there will never be gaps in the
data)?

Many thanks.

Keith.

Keith,

Again, it depends on which level object you have currently defined:

Dim oExcel As Excel.Application
Dim xlShtObj As Excel.Worksheet
Dim objRange As Excel.Range
.....

With xlShtObj
Set objRange = .Range(.Range("D3"), .Cells(Rows.Count,4).End(xlUp))
End With

will be the range of all cells in column D on the sheet object xlShtObj.

Of course, there are many ways of getting the same range:

With xlShtObj
Set objRange = .Range(.Range("D3"), .Cells(Rows.Count, 4).End(xlUp))
MsgBox objRange.Address
Set objRange = .Range(.Range("D3"), .Range("D3").End(xlDown))
MsgBox objRange.Address
Set objRange = Intersect(.Range("D3:D65536"), .UsedRange)
MsgBox objRange.Address
Set objRange = Intersect(.Range("D:D"), .Range("D3").CurrentRegion)
MsgBox objRange.Address
End With

and many other variations on those themes....

HTH,
Bernie
MS Excel MVP


"Keith Wilby" wrote in message ...
I want to format some cells in a spreadsheet using VBA from MS Access. The formatting code I have
is working fine (thanks to Bernie Deitrick) but the range of cells populated with data will change
over time. What code do I need to detect the range of populated cells (there will never be gaps in
the data)?

Many thanks.

Keith.

wrote in message
oups.com...
Use the CurrentRegion property of the range, eg

Dim myRange as Range
set myRange = Range("A1").CurrentRegion
myRange.Font.Size=12

assuming A1 is in the set of populated cells.


Perfect! Many thanks David.

Keith.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Keith,

Again, it depends on which level object you have currently defined:

Dim oExcel As Excel.Application
Dim xlShtObj As Excel.Worksheet
Dim objRange As Excel.Range
....

With xlShtObj
Set objRange = .Range(.Range("D3"), .Cells(Rows.Count,4).End(xlUp))
End With

will be the range of all cells in column D on the sheet object xlShtObj.

Of course, there are many ways of getting the same range:

With xlShtObj
Set objRange = .Range(.Range("D3"), .Cells(Rows.Count, 4).End(xlUp))
MsgBox objRange.Address
Set objRange = .Range(.Range("D3"), .Range("D3").End(xlDown))
MsgBox objRange.Address
Set objRange = Intersect(.Range("D3:D65536"), .UsedRange)
MsgBox objRange.Address
Set objRange = Intersect(.Range("D:D"), .Range("D3").CurrentRegion)
MsgBox objRange.Address
End With


Thanks very much for yet another great response Bernie. I've used David's
suggestion to select all populated cells to set the font size but what I
need now is to select all populated columns from, say, column J onwards to
set their width and apply conditional formatting. Which option would be
best suited?

Many thanks.

Keith.

Keith,

Try

With xlShtObj
Set objRange = Intersect(.Range("J:IV"), .Range("J3").CurrentRegion)
End With

would work well, if there entirely blank rows and columns delineating the range you want to work
with. Otherwise,

With xlShtObj
Set objRange = Intersect(.Range("J:IV"), .UsedRange)
End With


HTH,
Bernie
MS Excel MVP


"Keith Wilby" wrote in message ...
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Keith,

Again, it depends on which level object you have currently defined:

Dim oExcel As Excel.Application
Dim xlShtObj As Excel.Worksheet
Dim objRange As Excel.Range
....

With xlShtObj
Set objRange = .Range(.Range("D3"), .Cells(Rows.Count,4).End(xlUp))
End With

will be the range of all cells in column D on the sheet object xlShtObj.

Of course, there are many ways of getting the same range:

With xlShtObj
Set objRange = .Range(.Range("D3"), .Cells(Rows.Count, 4).End(xlUp))
MsgBox objRange.Address
Set objRange = .Range(.Range("D3"), .Range("D3").End(xlDown))
MsgBox objRange.Address
Set objRange = Intersect(.Range("D3:D65536"), .UsedRange)
MsgBox objRange.Address
Set objRange = Intersect(.Range("D:D"), .Range("D3").CurrentRegion)
MsgBox objRange.Address
End With


Thanks very much for yet another great response Bernie. I've used David's suggestion to select
all populated cells to set the font size but what I need now is to select all populated columns
from, say, column J onwards to set their width and apply conditional formatting. Which option
would be best suited?

Many thanks.

Keith.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Keith,

Try

With xlShtObj
Set objRange = Intersect(.Range("J:IV"), .Range("J3").CurrentRegion)
End With

would work well, if there entirely blank rows and columns delineating the
range you want to work with. Otherwise,

With xlShtObj
Set objRange = Intersect(.Range("J:IV"), .UsedRange)
End With



Perfect! Thanks Bernie.

Regards,
Keith.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Keith,

Try

With xlShtObj
Set objRange = Intersect(.Range("J:IV"), .Range("J3").CurrentRegion)
End With

would work well, if there entirely blank rows and columns delineating the
range you want to work with. Otherwise,

With xlShtObj
Set objRange = Intersect(.Range("J:IV"), .UsedRange)
End With



Just one last quickie Bernie, how do I adapt this

Set objRange = objXL.Intersect(objSht.Range("K:IV"), objSht.UsedRange)

such that it ignores the first row of the range? I've tried Range("K2:IV")
but that doesn't work (it doesn't help that I don't really know what that
snippet does).

Thanks as always.

Keith.

Keith,

To ignore the first row of the range, just use

Set objRange = objXL.Intersect(objSht.Range("K2:IV65536"), objSht.UsedRange)

The string passed to the Range object ("K2:IV65536") is the same as if you selected that range in a
function call.

HTH,
Bernie
MS Excel MVP


"Keith Wilby" wrote in message ...
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Keith,

Try

With xlShtObj
Set objRange = Intersect(.Range("J:IV"), .Range("J3").CurrentRegion)
End With

would work well, if there entirely blank rows and columns delineating the range you want to work
with. Otherwise,

With xlShtObj
Set objRange = Intersect(.Range("J:IV"), .UsedRange)
End With



Just one last quickie Bernie, how do I adapt this

Set objRange = objXL.Intersect(objSht.Range("K:IV"), objSht.UsedRange)

such that it ignores the first row of the range? I've tried Range("K2:IV") but that doesn't work
(it doesn't help that I don't really know what that snippet does).

Thanks as always.

Keith.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Keith,

To ignore the first row of the range, just use

Set objRange = objXL.Intersect(objSht.Range("K2:IV65536"),
objSht.UsedRange)

The string passed to the Range object ("K2:IV65536") is the same as if you
selected that range in a function call.


Many thanks again Bernie, that works perfectly but I'm still not sure what
the "IV" part of the argument does. I'm assuming that 65536 is the maximum
number of rows you can have, could you provide an idiot's guide please?
Thanks for your patience.

Regards,
Keith.

Keith,

IV is the column letters of the last column of the spreadsheet, and you're
right, the 65536 is the max rows.

Unless you have XL2007 - so I should probably have used something like:

Set objRange =
objXL.Intersect(objSht.Range(objSht.Cells(2,11),ob jSht.Cells(objSht.Rows.Count,objSht.Columns.Count) )

and watch the text wrapping on that.

Bernie

"Keith Wilby" wrote in message
...
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Keith,

To ignore the first row of the range, just use

Set objRange = objXL.Intersect(objSht.Range("K2:IV65536"),
objSht.UsedRange)

The string passed to the Range object ("K2:IV65536") is the same as if
you selected that range in a function call.


Many thanks again Bernie, that works perfectly but I'm still not sure what
the "IV" part of the argument does. I'm assuming that 65536 is the
maximum number of rows you can have, could you provide an idiot's guide
please? Thanks for your patience.

Regards,
Keith.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Keith,

IV is the column letters of the last column of the spreadsheet, and you're
right, the 65536 is the max rows.

Unless you have XL2007 - so I should probably have used something like:

Set objRange =
objXL.Intersect(objSht.Range(objSht.Cells(2,11),ob jSht.Cells(objSht.Rows.Count,objSht.Columns.Count) )

and watch the text wrapping on that.


Received and understood. Many thanks for all your help.

Regards,
Keith.
www.keithwilby.com