Checking for empty cells

[UHsoccer]

I want use the following code to test if a specific range is empty


If Not IsEmpty(Range("G11:H11")) Then - displays NOT empty

BUT

If Not IsEmpty(Range("G11:G11")) Then - displays empty
If Not IsEmpty(Range("H11:H11")) Then - displays empty

What is the syntax error in the first one

 

[macleanb]

This is because range is returning (by default) the value property - for a single cell this is the value, however for a range its going to be an array of values - which by definition isnt empty (though the individual elements may be)

so perhaps:

Application.WorksheetFunction.CountA(Range("A1:a5"))

 

[UHsoccer]

So I should do one cell at the time, or is there another property that I can query on to handle the range (which in reality is from column F to R)

 

[macleanb]

sorry - just updated the initial post

 

[UHsoccer]

used your (macleanb) code and for ONE cell is still thinks there is data

If Not IsEmpty(Application.WorksheetFunction.CountA(Range("G11:G11")))

with

If Not IsEmpty(Range("G11:G11")) it is empty

 

[macleanb]

What I meant was:


if Application.WorksheetFunction.CountA(Range("A1:a5")) > 0 then


....


endif

 

[UHsoccer]

That made a big difference, works great. Thanks