To make the nightmare complete, I should have also mentioned cells
formatted as "Text" vs. general - grrr.

Anyway, you know how you can go control-RightArrow (etc.) to go to the
next "unused" or "Used" cell when you're in (or out) of a clump? However
when you paste in a Ctl-C copy of an Access table or query, you don't
have that luxury. (Is it that all cells are "Used?")

I would like to write a quickie VBA utility to rectify this. Perhaps it
would achieve something of the order of

dim c as range
for all c in activesheet.UsedRange
if isnull(c) then c.value=Nothing
If c.Value < xlNull then c.value=Nothing
If c.value="" then c.value=Nothing
next

What would be the right way to do it? Is this way viable conceptually?

And does the cell format figure into this? Note that there is no
c.format, so I haven't seen a property for it. (Regardless, Redmond
needs to get its head out of you-know-where and provide another format
designation. E.g., sometimes data pasted into a cell formatted as text
is critically different than if pasted to general and then formatted to
text - but they appear to be the same. The mere 2 designations Text and
General are absurdly inadequate.)

Did I remember to say argggh?

Not sure I follow all your grrr's, but empty fields from Access text fields
will be imported or pasted as empty strings. Try this:

Sub test()
Dim cel As Range
For Each cel In Selection ' or ActiveSheet.UsedRange
If Len(cel) = 0 And IsEmpty(cel) = False Then
cel.ClearContents
End If
Next
End Sub

Above will also clear a formula like =A1 where the contents of A1 is "". So
you might also want to include an additional check in the If line:
"And cel.HasFormula = False "

Cell formats don't "figure into this", though formatted but otherwise empty
cells do impact the used-range.

Regards,
Peter T



"Wild Bill" wrote in message
.. .
To make the nightmare complete, I should have also mentioned cells
formatted as "Text" vs. general - grrr.

Anyway, you know how you can go control-RightArrow (etc.) to go to the
next "unused" or "Used" cell when you're in (or out) of a clump? However
when you paste in a Ctl-C copy of an Access table or query, you don't
have that luxury. (Is it that all cells are "Used?")

I would like to write a quickie VBA utility to rectify this. Perhaps it
would achieve something of the order of

dim c as range
for all c in activesheet.UsedRange
if isnull(c) then c.value=Nothing
If c.Value < xlNull then c.value=Nothing
If c.value="" then c.value=Nothing
next

What would be the right way to do it? Is this way viable conceptually?

And does the cell format figure into this? Note that there is no
c.format, so I haven't seen a property for it. (Regardless, Redmond
needs to get its head out of you-know-where and provide another format
designation. E.g., sometimes data pasted into a cell formatted as text
is critically different than if pasted to general and then formatted to
text - but they appear to be the same. The mere 2 designations Text and
General are absurdly inadequate.)

Did I remember to say argggh?

With ActiveSheet.UsedRange
.Value = .Value
End With

will clear out the blank-looking gunk. Can take a while on a large range.


"Wild Bill" wrote in message
.. .
To make the nightmare complete, I should have also mentioned cells
formatted as "Text" vs. general - grrr.

Anyway, you know how you can go control-RightArrow (etc.) to go to the
next "unused" or "Used" cell when you're in (or out) of a clump? However
when you paste in a Ctl-C copy of an Access table or query, you don't
have that luxury. (Is it that all cells are "Used?")

I would like to write a quickie VBA utility to rectify this. Perhaps it
would achieve something of the order of

dim c as range
for all c in activesheet.UsedRange
if isnull(c) then c.value=Nothing
If c.Value < xlNull then c.value=Nothing
If c.value="" then c.value=Nothing
next

What would be the right way to do it? Is this way viable conceptually?

And does the cell format figure into this? Note that there is no
c.format, so I haven't seen a property for it. (Regardless, Redmond
needs to get its head out of you-know-where and provide another format
designation. E.g., sometimes data pasted into a cell formatted as text
is critically different than if pasted to general and then formatted to
text - but they appear to be the same. The mere 2 designations Text and
General are absurdly inadequate.)

Did I remember to say argggh?

Brilliant! Looks perfect! Thanks!

On Sun, 1 May 2005 12:16:23 -0700, "Tim Zych"
wrote:

With ActiveSheet.UsedRange
.Value = .Value
End With

will clear out the blank-looking gunk. Can take a while on a large range.