Hi.

I'm trying to select only the blank cells in a selected range.

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.

"mark" wrote:

Hi.

I'm trying to select only the blank cells in a selected range.

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.

mark wrote:

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.

"mark" wrote:

Hi.

I'm trying to select only the blank cells in a selected range.



--

Dave Peterson

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.

"Dave Peterson" wrote:

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.

mark wrote:

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.

"mark" wrote:

Hi.

I'm trying to select only the blank cells in a selected range.



--

Dave Peterson

Hi

You could also use this:

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

Regards,
Per


On 7 Maj, 04:38, mark wrote:
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.



"Dave Peterson" wrote:
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.

mark wrote:

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.

"mark" wrote:

Hi.

I'm trying to select only the blank cells in a selected range.

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

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.

Per Jessen wrote:

Hi

You could also use this:

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

Regards,
Per

On 7 Maj, 04:38, mark wrote:
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.



"Dave Peterson" wrote:
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.

mark wrote:

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.

"mark" wrote:

Hi.

I'm trying to select only the blank cells in a selected range.

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

--

Dave Peterson