Concise Way To Tell If Range Contains Any Data?
 

I'm doing validation checking on a spreadsheet whose data will be
imported into a data base if the spreadsheet's formatting and layout
look right.

On a given worksheet, once I think I've found the last row of data,
I'd like to set a .Range=(a whole bunch of cells after what I think is
the last data row) and then check to see if there are any values in
the composite of all those cells.

If there are any values, that would suggest that the worksheet's
format is wanting and I'd inform the user accordingly.

Bottom Line: If I've got a Range, is there a syntactically-simple way
to see if there's any data in it?

Concise Way To Tell If Range Contains Any Data?
 

Hi Pete.

Try:

Set Rng = Range("A1:A100")

If Application.CountA(Rng) = 0 Then
MsgBox "Its empty!"
End If


---
Regards,
Norman


"PeteCresswell" wrote in message
ups.com...
I'm doing validation checking on a spreadsheet whose data will be
imported into a data base if the spreadsheet's formatting and layout
look right.

On a given worksheet, once I think I've found the last row of data,
I'd like to set a .Range=(a whole bunch of cells after what I think is
the last data row) and then check to see if there are any values in
the composite of all those cells.

If there are any values, that would suggest that the worksheet's
format is wanting and I'd inform the user accordingly.

Bottom Line: If I've got a Range, is there a syntactically-simple way
to see if there's any data in it?

Concise Way To Tell If Range Contains Any Data?
 

If Application.CountA(Rng) = 0 Then

That nailed it.... *exactly* what I was trolling for.

Thanks!

Concise Way To Tell If Range Contains Any Data?
 

Set Rng = Range("A1:A100")

No good deed goes unpunished.

At the risk of wearing out my welcome...

Can I write some kind of loop to iterate through that
range and pick off the Row/Column numbers and cell values
of the offending (i.e. those that have values) cells?

Then I could include at least some of the offending cell locations/
values in my error notification....

Concise Way To Tell If Range Contains Any Data?
 

Hi Pete,

Instead of looping, try usung the specialcells method,

For example, if the values did not include formulae, you
could try something like:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range

Set WB = Workbooks("MyBook.xls") '<<=== CHANGE
Set SH = WB.Sheets("Sheet1") '<<=== CHANGE
Set Rng = SH.Range("A1:A100") '<<=== CHANGE

On Error Resume Next
Set Rng = Rng.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not Rng Is Nothing Then
'do something, e.g.:
Debug.Print Rng.Address(0, 0)
End If
End Sub
'<<=============



---
Regards,
Norman


"PeteCresswell" wrote in message
ups.com...

Set Rng = Range("A1:A100")

No good deed goes unpunished.

At the risk of wearing out my welcome...

Can I write some kind of loop to iterate through that
range and pick off the Row/Column numbers and cell values
of the offending (i.e. those that have values) cells?

Then I could include at least some of the offending cell locations/
values in my error notification....