missing something

[Curt]

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

 

[Bob Phillips]

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)

 

[Ron Rosenfeld]

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).Select
'---------------------------





--ron

 

[Curt]

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

 

[Tom Ogilvy]

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.

 

[Curt]

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

 

[JMB]

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]

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

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

 

[Curt]

Thanks Tom:
Works great. Got a strange one in K6 have tried to format etc no avail. When
I run this and blanks it draws another cell in same as a smaller border in
cell K6. All other cells do what they should. I am not sure if this is an
excel qlich or not. K6 does not gray out as others do.
Can live with it just screwey. The cell is highly visible so no matter.
Thanks Again

 

[Dave Peterson]

Maybe the user hit 2 spaces (or 3 or 4 or ...) to "clear" that cell.

I used 20 in this code, but you may want to make it as large as you think is
necessary.

Dim HowMany As Long
Dim iCtr As Long

HowMany = 20 'as large as you like

With ActiveSheet
For iCtr = 1 To HowMany
.Columns("C:C").Replace _
What:=Space(iCtr), _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr
End With

====
If that doesn't help, Chip Pearson has a very nice addin that will help
determine what those characters are:
http://www.cpearson.com/excel/CellView.htm

You'll be able to find out exactly what's in that cell.

 

[Curt]

will see what pearson has to say. Will be nice to find out what it is. Will
post when I find out.

 

[Curt]

This is wierd. used Pearson shows nothing in cell. only appears useing
special blanks shading. As soon as you hit return to clear it is gone. I used
100 in your code. Have tried every format command etc. Stumped. It is highly
visable as a blank so can live with it. Just like to know what is happening.
Thanks much

 

[Dave Peterson]

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

=======================

In code:

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

.Replace _
What:="$$$$$", _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
End With