Hi,

I'm looking for a foolproof way of identify empty cells within VBA code.
For example I've written code that extracts the first two digits from a
postcode but the code fails when I encounter an empty cell. I've tried
building in If statements with isempty, isblank and Null and " " but its
doesn't seem to work 100% of the time. Any suggestions would be greatly
appreciated.

Regards

David

Try LEN

If Len(cell) = 0 Then
...

--

HTH

RP
(remove nothere from the email address if mailing direct)


"David" wrote in message
...
Hi,

I'm looking for a foolproof way of identify empty cells within VBA code.
For example I've written code that extracts the first two digits from a
postcode but the code fails when I encounter an empty cell. I've tried
building in If statements with isempty, isblank and Null and " " but its
doesn't seem to work 100% of the time. Any suggestions would be greatly
appreciated.

Regards

David

David,
Try using the Trim function.

If Trim(Range(CurrentCell)) = "" then

end if

It's worked well for me.

If the cell is really empty (no formulas, too!):

if isempty(mycell) then
''''

If the cell contains a formula that returns "":
if mycell.value = "" then

If you really use a spacebar to "empty" a cell:
if trim(mycell.value) = "" then

(Using a spacebar to make the cell look empty can cause lots of trouble. I
think it's better to just hit the delete key on the keyboard (or
Edit|Clear|Contents)

David wrote:

Hi,

I'm looking for a foolproof way of identify empty cells within VBA code.
For example I've written code that extracts the first two digits from a
postcode but the code fails when I encounter an empty cell. I've tried
building in If statements with isempty, isblank and Null and " " but its
doesn't seem to work 100% of the time. Any suggestions would be greatly
appreciated.

Regards

David

--

Dave Peterson