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?

Hi Pete.

Try:

Set Rng = Range("A1:A100")

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


---
Regards,
Norman


"PeteCresswell" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>

> If Application.CountA(Rng) = 0 Then

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

Thanks!

> 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....

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
>> 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....
>