xlCellTypeBlanks

  • Thread starter Thread starter mark
  • Start date Start date
sorry... bumped the wrong key.

so like I was saying, I'm trying to select only the blank cells in a
selected range, using this line:

Selection.SpecialCells(xlCellTypeBlanks).Select

But, in situations with a small input set, there are times when there are no
blank cells.

I was hoping I could check:

Selection.SpecialCells(xlCellTypeBlanks).Count

But that doesn't work... it errors out and still says 'No Cells Found",
instead of telling me 0.

I could probably work in an error handler, but is there another way without
that that someone sees, which I'm not thinking of?

Thanks.
 
Another way:

Dim myRng as range
on error resume next
set myrng = Selection.SpecialCells(xlCellTypeBlanks)
on error goto 0

if myrng is nothing then
msgbox "0 blank cells found"
else
msgbox myrng.cells.count
myrng.select 'do you really want to select the cell
end if

====
If the selection could be a single cell, I'd use:

set myrng = intersect(selection, selection.cells.specialcells(xlcelltypeblanks))

actually, I'd use this all the time -- just to be safe.
 
thanks... that's more along the line of what I might have come up with with
the error handler... but I hadn't started into it... forgot about this before
I left work today.

see how it works, though... thanks.
 
Hi

You could also use this:

If WorksheetFunction.CountBlank(Selection) > 0 Then
Selection.SpecialCells(xlCellTypeBlanks).Select
End If

Regards,
Per
 
But there are differences.

=countblank()
will count cells that look empty but have formulas that evaluate to "" (or were
formulas that evaluated to "" and later converted to values).

And Specialcells does limit itself to the usedrange which may be important if
the selection is a single cell.

I don't think I'd use this without more checks.
 
Thanks guys. I see how both work.

In my particular use of it, either will do the job.

The subroutine where I'm using it is one which populates the blank cells
that happen when data is grouped. My particular input is from another
system, but it's like what happens when a Pivot Table has data grouped with
other row fields... the first (or second, third, etc) field has a value
listed once, and then additional rows which are blank in that column, until
the value of the first row field changes.

My routine selects those blank cells (where the value of the entry has not
changed, but is meant to be the same as the next entry above it), and fills
them in properly.

Thanks again.
Mark
 
right... basically the same thing.

mine takes an input parameter that tells how many columns you want it to do.

but basically that's it.
 
Back
Top