Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank cells corrupt?
Hello,
I received a simple spreadsheet from a client, mainly consisting of a column of numbers. The numbers are arranged in groups of 10, each group is separated by a blank cell. Here’s the problem – if I try to select those blank cells, either by GotoSpecialBlanks from the menu, or r.SpecialCells (xlCellTypeBlanks).Select via VBA, I get a “No cells were found” error message. And yet r.Find("").Select works just fine. There are no formulas in these cells, or anything else out of the ordinary that I can see. If I click in the formula bar & hit Enter, everything works as expected. Also, stepping thru the blank cells via VBA and making a change also fixes it. Can someone suggest to me what may be the problem, and how those cells could have got into that state in the first place? Thank you. Regards, DaveU |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank cells corrupt?
Your problem is the "blank" cells are not genuine empties. These cells are
easy to create for demonstration purposes: 1. pick a cell, say A1 and clrear it with Edit Clear all 2. then enter a null with ="" 3. then copy A1 and paste/special/values back onto A1 At this point, A1 APPEARS to be empty, both in the cell and in the formula bar. However, =ISBLANK(A1) returns FALSE!! This type of "pseudo-blank" cell will not be found by goto special blanks. You can also create "pseudo-blanks" with VBA: Sub demo() Set a1 = Range("A1") a1.NumberFormat = "@" a1.Value = "" End Sub -- Gary''s Student - gsnu200909 "Dave Unger" wrote: Hello, I received a simple spreadsheet from a client, mainly consisting of a column of numbers. The numbers are arranged in groups of 10, each group is separated by a blank cell. Heres the problem €“ if I try to select those blank cells, either by GotoSpecialBlanks from the menu, or r.SpecialCells (xlCellTypeBlanks).Select via VBA, I get a €śNo cells were found€ť error message. And yet r.Find("").Select works just fine. There are no formulas in these cells, or anything else out of the ordinary that I can see. If I click in the formula bar & hit Enter, everything works as expected. Also, stepping thru the blank cells via VBA and making a change also fixes it. Can someone suggest to me what may be the problem, and how those cells could have got into that state in the first place? Thank you. Regards, DaveU . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank cells corrupt?
Saved from a previous post:
If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all In code you could do something like: Option Explicit Sub testme() With ActiveSheet With .cells 'or a specific range: With .Range("D:D") .Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False .Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False End With End With End Sub Dave Unger wrote: Hello, I received a simple spreadsheet from a client, mainly consisting of a column of numbers. The numbers are arranged in groups of 10, each group is separated by a blank cell. Here’s the problem – if I try to select those blank cells, either by GotoSpecialBlanks from the menu, or r.SpecialCells (xlCellTypeBlanks).Select via VBA, I get a “No cells were found” error message. And yet r.Find("").Select works just fine. There are no formulas in these cells, or anything else out of the ordinary that I can see. If I click in the formula bar & hit Enter, everything works as expected. Also, stepping thru the blank cells via VBA and making a change also fixes it. Can someone suggest to me what may be the problem, and how those cells could have got into that state in the first place? Thank you. Regards, DaveU -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank cells corrupt?
Hello Gary”s Student & Dave
Gary”s Student, thanks for the excellent explanation – yes, that’s exactly how the client built the sheet, using copy/pastespecial/values methods. Dave, thanks for the Toggle Transition hint – I’ve never used this before, but I see it can be a useful tool. Also appreciate the cleanup suggestions. While I don’t pretend to be an expert, I have been coding in VBA for a few years, but have never encountered this before. It quite took me by surprise, as I thought I had “mastered” this area. One never stops learning, do they? Again, thanks so much to both of you, very much appreciated. Regards, DaveU |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Array Formula for Counting Blank & Non Blank Cells (Multiple Criteria) | Excel Discussion (Misc queries) | |||
Excel 2002: Return blank when VLOOKUP on blank cells | Excel Discussion (Misc queries) | |||
Corrupt Cells? | Excel Discussion (Misc queries) | |||
Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming |