useing this it does not shade all blank cells havent been able to figure what
is missing also it does not clear contents in range of blanks. Object I am
trying for notify user about blank cells. User has a way around entry to
create blanks cannot stop there. Best I know.
Thanks Much

Sub blanks()
' blanks Macro
' Macro recorded 6/28/2007 by Curtiss A. Greer
Worksheets("Data").Activate
Range("d5:m60").Select
Selection.specialcells(xlCellTypeBlanks).Select
Selection.ClearContents
MsgBox "Empty cells must be filled"
End Sub

It will only select the blank cells if they are in the worksheet's used
range.

And why are you trying to clear cells that you have selected as blank cells,
they are already clear?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Curt" wrote in message
...
useing this it does not shade all blank cells havent been able to figure
what
is missing also it does not clear contents in range of blanks. Object I am
trying for notify user about blank cells. User has a way around entry to
create blanks cannot stop there. Best I know.
Thanks Much

Sub blanks()
' blanks Macro
' Macro recorded 6/28/2007 by Curtiss A. Greer
Worksheets("Data").Activate
Range("d5:m60").Select
Selection.specialcells(xlCellTypeBlanks).Select
Selection.ClearContents
MsgBox "Empty cells must be filled"
End Sub

I am trying to have the cells blank. What happens is if a user blanks a cell
with the space bar it doesnot see that as blank. When i go back and use clear
contents or delete then it sees it as blank. This is the reason for this.
Sorry I didn't relate my problem very well
Thanks

"Bob Phillips" wrote:

It will only select the blank cells if they are in the worksheet's used
range.

And why are you trying to clear cells that you have selected as blank cells,
they are already clear?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Curt" wrote in message
...
useing this it does not shade all blank cells havent been able to figure
what
is missing also it does not clear contents in range of blanks. Object I am
trying for notify user about blank cells. User has a way around entry to
create blanks cannot stop there. Best I know.
Thanks Much

Sub blanks()
' blanks Macro
' Macro recorded 6/28/2007 by Curtiss A. Greer
Worksheets("Data").Activate
Range("d5:m60").Select
Selection.specialcells(xlCellTypeBlanks).Select
Selection.ClearContents
MsgBox "Empty cells must be filled"
End Sub

But, your command would not pick up the cells cleared with a spacebar because
they are not blank. (and you specify that it select blank cells).

To find those cells, you would have to loop through them and check them.

--
Regards,
Tom Ogilvy


"Curt" wrote:

I am trying to have the cells blank. What happens is if a user blanks a cell
with the space bar it doesnot see that as blank. When i go back and use clear
contents or delete then it sees it as blank. This is the reason for this.
Sorry I didn't relate my problem very well
Thanks

"Bob Phillips" wrote:

It will only select the blank cells if they are in the worksheet's used
range.

And why are you trying to clear cells that you have selected as blank cells,
they are already clear?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Curt" wrote in message
...
useing this it does not shade all blank cells havent been able to figure
what
is missing also it does not clear contents in range of blanks. Object I am
trying for notify user about blank cells. User has a way around entry to
create blanks cannot stop there. Best I know.
Thanks Much

Sub blanks()
' blanks Macro
' Macro recorded 6/28/2007 by Curtiss A. Greer
Worksheets("Data").Activate
Range("d5:m60").Select
Selection.specialcells(xlCellTypeBlanks).Select
Selection.ClearContents
MsgBox "Empty cells must be filled"
End Sub

Talked to a friend and he opened my eyes about space bar not being blank. Am
now trying to find a way to search for space symbol in help excel no luck
yet.
Thanks Tom
Will keep searching


"Tom Ogilvy" wrote:

But, your command would not pick up the cells cleared with a spacebar because
they are not blank. (and you specify that it select blank cells).

To find those cells, you would have to loop through them and check them.

--
Regards,
Tom Ogilvy


"Curt" wrote:

I am trying to have the cells blank. What happens is if a user blanks a cell
with the space bar it doesnot see that as blank. When i go back and use clear
contents or delete then it sees it as blank. This is the reason for this.
Sorry I didn't relate my problem very well
Thanks

"Bob Phillips" wrote:

It will only select the blank cells if they are in the worksheet's used
range.

And why are you trying to clear cells that you have selected as blank cells,
they are already clear?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Curt" wrote in message
...
useing this it does not shade all blank cells havent been able to figure
what
is missing also it does not clear contents in range of blanks. Object I am
trying for notify user about blank cells. User has a way around entry to
create blanks cannot stop there. Best I know.
Thanks Much

Sub blanks()
' blanks Macro
' Macro recorded 6/28/2007 by Curtiss A. Greer
Worksheets("Data").Activate
Range("d5:m60").Select
Selection.specialcells(xlCellTypeBlanks).Select
Selection.ClearContents
MsgBox "Empty cells must be filled"
End Sub

Columns("C:C").Replace _
What:=" ", _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False

should make the cells blank. Then you can use your special cells.

--
Regards,
Tom Ogilvy


"Curt" wrote:

Talked to a friend and he opened my eyes about space bar not being blank. Am
now trying to find a way to search for space symbol in help excel no luck
yet.
Thanks Tom
Will keep searching


"Tom Ogilvy" wrote:

But, your command would not pick up the cells cleared with a spacebar because
they are not blank. (and you specify that it select blank cells).

To find those cells, you would have to loop through them and check them.

--
Regards,
Tom Ogilvy


"Curt" wrote:

I am trying to have the cells blank. What happens is if a user blanks a cell
with the space bar it doesnot see that as blank. When i go back and use clear
contents or delete then it sees it as blank. This is the reason for this.
Sorry I didn't relate my problem very well
Thanks

"Bob Phillips" wrote:

It will only select the blank cells if they are in the worksheet's used
range.

And why are you trying to clear cells that you have selected as blank cells,
they are already clear?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Curt" wrote in message
...
useing this it does not shade all blank cells havent been able to figure
what
is missing also it does not clear contents in range of blanks. Object I am
trying for notify user about blank cells. User has a way around entry to
create blanks cannot stop there. Best I know.
Thanks Much

Sub blanks()
' blanks Macro
' Macro recorded 6/28/2007 by Curtiss A. Greer
Worksheets("Data").Activate
Range("d5:m60").Select
Selection.specialcells(xlCellTypeBlanks).Select
Selection.ClearContents
MsgBox "Empty cells must be filled"
End Sub

Perhaps the OP could use data validation to prevent users from using the
space bar to "blank" a cell.

Data/Validation/Custom

Formula is =len(substitute(a1," ",""))0

(assuming the active cell is A1)


"Tom Ogilvy" wrote:

But, your command would not pick up the cells cleared with a spacebar because
they are not blank. (and you specify that it select blank cells).

To find those cells, you would have to loop through them and check them.

--
Regards,
Tom Ogilvy


"Curt" wrote:

I am trying to have the cells blank. What happens is if a user blanks a cell
with the space bar it doesnot see that as blank. When i go back and use clear
contents or delete then it sees it as blank. This is the reason for this.
Sorry I didn't relate my problem very well
Thanks

"Bob Phillips" wrote:

It will only select the blank cells if they are in the worksheet's used
range.

And why are you trying to clear cells that you have selected as blank cells,
they are already clear?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Curt" wrote in message
...
useing this it does not shade all blank cells havent been able to figure
what
is missing also it does not clear contents in range of blanks. Object I am
trying for notify user about blank cells. User has a way around entry to
create blanks cannot stop there. Best I know.
Thanks Much

Sub blanks()
' blanks Macro
' Macro recorded 6/28/2007 by Curtiss A. Greer
Worksheets("Data").Activate
Range("d5:m60").Select
Selection.specialcells(xlCellTypeBlanks).Select
Selection.ClearContents
MsgBox "Empty cells must be filled"
End Sub

On Sun, 1 Jul 2007 08:36:00 -0700, Curt wrote:

useing this it does not shade all blank cells havent been able to figure what
is missing also it does not clear contents in range of blanks. Object I am
trying for notify user about blank cells. User has a way around entry to
create blanks cannot stop there. Best I know.
Thanks Much

Sub blanks()
' blanks Macro
' Macro recorded 6/28/2007 by Curtiss A. Greer
Worksheets("Data").Activate
Range("d5:m60").Select
Selection.specialcells(xlCellTypeBlanks).Select
Selection.ClearContents
MsgBox "Empty cells must be filled"
End Sub

Your macro is redundant -- but that's because you've recorded it.

However, it does seem to select the blank cells in a test range, and, of
course, with the selection, they are shaded.

Some questions, though.

Why would you bother to ClearContents since the cell, by definition, is already
blank? And how do you know it is not executing that method on blank cells in
your macro?

Simpler, if you want to select blank cells, would be just

'---------------------------
Range("d5:m60").SpecialCells(xlCellTypeBlanks).Sel ect
'---------------------------





--ron