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.

Try this

Worksheetfunction.CountBlank(selection)
--
If this post helps click Yes
---------------
Jacob Skaria


"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.

That'll work great, thanks.

You get a "Yes" button push for that.

"Jacob Skaria" wrote:

Try this

Worksheetfunction.CountBlank(selection)
--
If this post helps click Yes
---------------
Jacob Skaria


"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.

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

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

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

Like this from Debra Dalgleish's site:
http://contextures.com/xlDataEntry02.html
http://www.contextures.com/xlVideos01.html#FillBlanks

mark wrote:

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

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


--

Dave Peterson

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.

"Dave Peterson" wrote:

Like this from Debra Dalgleish's site:
http://contextures.com/xlDataEntry02.html
http://www.contextures.com/xlVideos01.html#FillBlanks

mark wrote:

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

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


--

Dave Peterson