Selecting Populate Cells

[Keith Wilby]

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.

 

[david.ritchie.au]

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

 

[Bernie Deitrick]

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("D365536"), .UsedRange)
MsgBox objRange.Address
Set objRange = Intersect(.Range("D"), .Range("D3").CurrentRegion)
MsgBox objRange.Address
End With

and many other variations on those themes....

HTH,
Bernie
MS Excel MVP

 

[Keith Wilby]

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.

 

[Keith Wilby]

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("D365536"), .UsedRange)
MsgBox objRange.Address
Set objRange = Intersect(.Range("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]

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]

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.

 

[Keith Wilby]

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]

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]

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]

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),objSht.Cells(objSht.Rows.Count,objSht.Columns.Count))

and watch the text wrapping on that.

Bernie

 

[Keith Wilby]

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),objSht.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