Concise Way To Tell If Range Contains Any Data?

  • Thread starter Thread starter PeteCresswell
  • Start date Start date
P

PeteCresswell

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
 
If Application.CountA(Rng) = 0 Then

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

Thanks!
 
Set Rng = Range("A1:A100")
Click to expand...

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
'<<=============
 
Back
Top