blank cells which aren’t! (2000/2002)

Topic

New Reply

I have data which is imported into Excel from Access. There are cells which look empty and should be empty- but in fact arn’t so when I try to write a function which uses ISBLANK() – it does not include them.

Is there anyway (preferably easy!) of overcoming this please?

Reply | Quote

Replies

Try the following macro:

Sub DeleteEmptyStrings()
Dim aCell As Range
On Error GoTo Exit_Sub
Application.ScreenUpdating = False
For Each aCell In Selection
If aCell.Value = “” Then aCell.ClearContents
Next
Exit_Sub:
Application.ScreenUpdating = True
End Sub

Reply | Quote

Your code will not help cells that look blank because they contain spaces. I would modify your code like this:

Sub DeleteEmptyStrings()
    Dim aCell As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    For Each aCell In Selection
        If Trim(aCell.Value) = "" Then aCell.ClearContents
    Next aCell
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

Reply | Quote

Yes, in general that’s better. I didn’t bother in this particular case, because it’s unlikely that cells imported from Access contain only spaces. Access trims trailing spaces itself. Your version is preferable as a general utility.

Reply | Quote

What is Access sending that Excel imports that looks blank but isn’t, and that your code would fix? I am really curious as to how your code actually does anything at all. It looks like your code clears the cell contents to “” if it is already equal to “”.

Reply | Quote

Frankly, I don’t really know. But I’ve come across several spreadsheets with imported data (from Access, DBase, …) where cells seemed to be empty (there were definitely NO spaces), but were treated by Excel as non-empty. The macro I posted earlier takes care of this. So apparently, ClearContents does something more than setting the value to “”. Again, I don’t really understand what or how or why, but it works for me…

Reply | Quote

I’m encountering a similar nuisance with imports from Access, most recently with percentages imported as text, but sometimes with null fields which are seen as null text (or some thing), even though they are formatted as general. I have attached a tiny sample of one; cells which appear blank in the range A2 – P49 are not. range.ClearContents does seem to be the only way to fix the problem, and I have a macro that addresses this specific monthly import.

Edit follows[/b]
On going back to review, I find I don’t use .ClearContents; the applicable lines in the import specific macro are:

With Selection
.NumberFormat = “$#,##0.00”
End With
For Each rngCell In Selection
If Len(rngCell) = 0 Then rngCell.Value = rngCell.Value
Next rngCell

Looks meaningless but it clears up the problem

Reply | Quote

I know a small trick

I do not know if the translation is the correct one, but I believe that is understood the steps .

Two forms: menu or VBA

Menu
1. you select your ranges of data
2. menu “data”, “text in columns

Reply | Quote

Neat trick! Except it only takes one column at a time.

(BTW, translation to English Excel:

2. menu “Data, Text to Columns

Reply | Quote

Sounds to me that what may be being imported from Access are Null values. Not that I have tested this, but there does not seem, on the face of it, anything alse in an Access field that would deliver such a performance in Excel.

Reply | Quote

you really are a life saver today!!!!!

Thanks

Reply | Quote

That is very interesting:

=IF(A2=””,”Y”,”N”)

results in Y

=ISBLANK(A2)

results in False

=CODE(A2)

results in #Value

=LEN(A2)

results in 0

=ISNONTEXT(A2)

results in False

=ISTEXT(A2)

results in True

=ISNUMBER(A2)

results in False

There is obviously something very strange in those cells.

Reply | Quote

Um, ah, you responded to Roberta but were you looking at my attachment when you wrote these comments?

I messed around with these “not-really-blank” cells for some time trying to figure out what they were, and gave up, choosing to write an extended version of the macro above to clean ’em up.

Reply | Quote

As far as I can see they’re just nullstrings.

Reply | Quote

(After messing around frantically for 30 minutes …) You’re right!

How did you deduce that?

Reply | Quote

Try this post. Like the “back to the roots” pic.

Reply | Quote

Hey, didn’t mean to disrespect the Unkamunka! But it helps to know more specifically that they are all vbNullStrings. I proved Rory’s assertion for my own satisfaction by writing a custom function:

Function ISNULLSTRING(rngCell As Range) As String
ISNULLSTRING = IIf(rngCell.Value = vbNullString, “NULLSTRING”, “NOT NULLSTRING”)
End Function

But I still want to know how you and Rory figured it out. (Not to mention why they get imported. Remember this?

‘Uh-huh. I know what you’re thinking. Did he fire six shots or only five? Well, to tell you the truth, I forgot myself in all this excitement. But being as this is the .44 Magnum, the most powerful handgun in the world and could blow your head clean off, you have to ask yourself one question, “Do I feel lucky?” Well, do you, punk?’

The punk surrenders, after which Dirty Harry starts to walk away, and the killer pleads “I gots to know” (how many bullets left). Harry points the 44 at him and pulls the trigger. The chamber is empty.

Well, I just gots to know.)

Isn’t it wonderful that the Excel lounge can bring you great moments in Cinema?

Reply | Quote

The thing – which it takes lots of time to wrap the brain cells around – is that Null and Nothing are not the same thing. You can occasionally create Null values in Excel. (They can show up, for example, when you do a sort.) Access, however, is used to working with Null Values and they are part of the general “landscape” of any database. Thus, they are an expectable issue when doing an import to Excel. Semantically, they are a value/string – even if they are Null.

So when are you going to start posting on the Access Board?

Thanks very much for the chuckles. This is my favorite non-technical post on a “serious” Board (followed closely by Legare and his floppy disks). Periodically, your post inspires me to think of posting the 97 and 2000 versions of all those customised shortcut menus I run (though most of them may be “been there, done that”). Maybe I’ll get around to it sometime.

Reply | Quote

All I do related to Access is export files for analysis in Excel; and I can write the simple queries I need. I’m more likely to get active in Outlook right now, due to a couple of frustrations with OL2k.

If you have any good shortcuts and shortcut menus by all means post them. I’m thinking of posting a couple of macros, figuring if I needed them someone else might one day.

Reply | Quote

There’s some good code (mostly from Jefferson) over on the Outlook Board – although the object model is a bit “eigenartig” (closest English translation = ornery). (Once you’ve wrapped your head around ActiveInspector, you may be able to reduce your click time!) http://www.slipstick.com[/url%5D is another good place to start.

The shortcuts/menus were originally written in 97 and then polished in 2000. Most of the final rough edges have gone – with some thanks to Brooke (and Rory). It needs a final clean, with a checkback into the 97 version before I feel comfortable posting them. I won’t forget.

Reply | Quote

a bit “eigenartig” (closest English translation = ornery)

I believe the translation is ‘peculiar’.

Aladin

Reply | Quote

I hate to disagree but Null is not a string. A Null string is a string but that’s a separate thing. By any normal definition I wouldn’t even say Null was a value (though people often refer to Null values) – you cannot for example expect something like:
if x = null then…
to work even if x is null. A null string on the other hand is a simple zero-length string, as in this case.
FWIW.

Reply | Quote

While I would suggest that Aladin’s definition of “eignartig” was bowdlerised (from Austrian German at least), YOU’RE bringing up something different.

Null is not really a value – agreed. What I had been inclined to believe is that these “items” were Nulls in the Access source. If you’re saying that they arrive in Excel as Null strings, that is a more precise definition than I was attempting. If you are saying that this only arises when the “items” are Null strings in the Access source, then I stand gratefully corrected.

Reply | Quote

As far as I know it depends on how you extract the data from Access in the first place. If you export using the standard Analyze with Excel toolbar button, Nulls and Null strings all appear in Excel as Null Strings. In this circumstance ISBLANK will return False and TypeName will produce String. If you extract the data using an Excel query (and I imagine DAO or ADO), Nulls arrive as Nulls (ISBLANK = True and TypeName = Empty) whereas Null strings arrive as before.
Having said all that, the testing I was doing yesterday (doing a simple = vbNullString comparison) was inconclusive as it in fact returns true for any blank cell – as does the if(A1=””,”Y”,”N”) type test.
So to summarise, it depends!

Reply | Quote

Yes, and my UDF is rubbish, as it returns NULLSTRING for a blank cell. I’m still unclear on how you diagnosed this, and what if any, modifications I need to my cleanup macro.

Reply | Quote

I don’t think you need any modifications to your macro for clean up purposes.
I admit I started out assuming they were null strings (data was coming from Access and ISBLANK returned FALSE, so…) and then tried to prove it!
If you try:
Function celltype(rngRange) As String
celltype = TypeName(rngRange.Value)
End Function
you will get “String” for your mystery cells and “Empty” for blank cells.
I should also point out that null strings and zero-length strings are not exactly the same thing, but near enough in this context.

Reply | Quote

Just a of side thought to the problem of identifying cells with blanks.
One method – not using VBA would be to apply a conditional format to cells with blanks inside them. – Have them circle themselves in red.

Alternately a VBA method for the same thing:

Sub findspace()
    ActiveSheet.UsedRange.Select
    Selection.FormatConditions.Delete
        Selection.FormatConditions.Add Type:=xlCellValue,  _
            Operator:=xlEqual,  Formula1:="="" """
    Selection.FormatConditions(1).Interior.ColorIndex = 6
End Sub

Reply | Quote