Can anyone suggest what is in the "blank" cells?
Can anyone suggest what is in the "blank" cells?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
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
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
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!
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
This line should delete blanks from the filtered list, but it's not finding any.
[VBA]
rngState.SpecialCells(xlCellTypeBlanks).Delete
[/VBA]
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
I found and removed a blank cell like this
[VBA]
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
[/VBA]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
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
Thanks Simon,
I can use that to remove the blank from the filtered list, [VBA]rngState.Find(What:="", LookAt:=xlWhole).Delete[/VBA]but I'm still stumped as to the cause.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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!
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
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.
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
Malcomb,
This was imported from Access. HTH
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'.
Not an option Norie.
Simon's suggestion will solve the practical problem, so it's just the mystery of what's in it that remains.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Why isn't it an option?
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...
Glen
Hi allOriginally Posted by moa
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.
[vba]
With Range("A1:A32542")
.Cells.NumberFormat = "@"
.AutoFilter Field:=1, Criteria1:="="
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Cells.NumberFormat = "General"
End With
[/vba]
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
Posting Permissions
Reply With Quote
