PDA

View Full Version : Solved: Blank cells which are not empty



mdmackillop
11-25-2006, 07:34 AM
Can anyone suggest what is in the "blank" cells?

Simon Lloyd
11-25-2006, 08:02 AM
Malcom, as you are well aware i'm no expert, but i looked at your example, iand of course there is nothing in them, if you use autofilter it will show the blanks in the range as well as the rest, your code does not delete or keep the filtered copy of Column A. If you posted this because you were getting a blank at the top of your list that you paste in to C2 i found the problem to be with A705 i cleared all formats as the cell was showing "Wrap Text" and when i ran your filter button there was no longer a blank at the top of the list.

If i have totally missed the problem could you explain a little more?

Regards,
Simon

Simon Lloyd
11-25-2006, 08:09 AM
belay that last post of mine, i closed the workbook without saving changes went back and cleared formats on those 2 cells you mentioned and still have blank at top of list now!

Regards,
Simon

Simon Lloyd
11-25-2006, 08:11 AM
I just went back to the other 2 blanks showing up in your list cleared the formats for those re-run your code and now there is no blank.

I could be leading you astray .... but thats what cured it for me!

Reagrds,
Simon

EDIT: Just checked again and its definately the formats of the cells that is causing it to act as though t has content when you search for unique records!

mdmackillop
11-25-2006, 08:15 AM
This line should delete blanks from the filtered list, but it's not finding any.

rngState.SpecialCells(xlCellTypeBlanks).Delete


Crl + Up or Down does not stop at the blanks. They return a count for both CountA and CountBlank. =Code(A585) returns a #VALUE error.:dunno

mdmackillop
11-25-2006, 08:25 AM
I've cleared wrap text from the whole of Column A, but the CountA is still showing the original value, and I'm not getting the Blank deleted.

XLGibbs
11-25-2006, 08:29 AM
Curious. It refuses to delete the blank cells from either list on my end. Even though it counts 4 (even in the code step by step) it comes up with "no cells found" as an error.

I even ran a mini loop to delete cells in the range, after sorting column A descending that had a len = 0 and it Still pulled the blank cell into the advanced filter.

Simon Lloyd
11-25-2006, 08:48 AM
I found and removed a blank cell like this

Sub Macro2()
'Range("A1:A32542").SpecialCells(xlCellTypeBlanks).Select
Dim Rng As Range
Set Rng = Range("A1:A32542")
With Rng
Cells.Find(What:="", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Delete
End With
End Sub
I havent made it loop through all the rest of the cells but it found the blank cells that show up in your list!

Regards,
Simon

mdmackillop
11-25-2006, 09:00 AM
Thanks Simon,
I can use that to remove the blank from the filtered list, rngState.Find(What:="", LookAt:=xlWhole).Deletebut I'm still stumped as to the cause.

Simon Lloyd
11-25-2006, 09:09 AM
Malcom could it be that once the cell has had data in and then removed it is no longer a special cell? have you tried inserting a brand new cell (or row) in to your range and using your code to see if it removes the new blank with your old code?

Just a thought!

Regards,
Simon

P.S It's nice to be able to give something back to you!

XLGibbs
11-25-2006, 10:12 AM
I have seen this with data that is imported from Access occassionally, or even data imported from a crystal report or pasted data from a text file.

Sometimes "clearing" the cell requires some kind of "action"... very strange.

austenr
11-25-2006, 10:20 AM
Malcomb,

This was imported from Access. HTH

Norie
11-25-2006, 01:36 PM
If this data is imported from Access and the purpose is to get a unique list then why not do that in Access with a simple query.

That could probably be used to eliminate the 'spaces'.

austenr
11-25-2006, 01:40 PM
Not an option Norie.

mdmackillop
11-25-2006, 01:43 PM
Simon's suggestion will solve the practical problem, so it's just the mystery of what's in it that remains.

Norie
11-25-2006, 01:47 PM
Why isn't it an option?

moa
11-29-2006, 09:11 AM
There may be a control character in the cells. You could write a loop to check through ascii characters against the contents of the cell...

Hellboy
11-29-2006, 10:38 AM
There may be a control character in the cells. You could write a loop to check through ascii characters against the contents of the cell...

Hi all

I came up with someting like this to resolve the problem after the extraction in Excel. But to do it before it appears like that, I dont know how. Like everyone notice, it looks like the format of the cell is somehow seen as something, but what, I do no. :(


With Range("A1:A32542")
.Cells.NumberFormat = "@"
.AutoFilter Field:=1, Criteria1:="="
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Cells.NumberFormat = "General"
End With

CBrine
11-29-2006, 12:08 PM
Malcome,
It looks like the cells have been formated as text(Not a normal text format, more like a text to columns text format). Do a text to columns....general on your column, and then run your code. Should work fine.

I've seen this before, mostly when using vlookups(They won't match), on legacy data imports to excel. Not sure what causes it, just how to fix it.

HTH
Cal