Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear "Blank" cells
Hello all,
I have found, in a number of my workbooks, ranges that contain cells that appear to be blank, but excel treats them as though they have data. When I sort a column ascending, these cells end up at the top of my list. Selecting and clearing them removes whatever value is hidden there and they no longer sort to the top of my lists. I'd like to be able to find them and clear all of them in my workbook but I can't figure out how to "find" them. Thoughts? Steven |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear "Blank" cells
Try this:
Sub cleanup() For Each r In ActiveSheet.UsedRange If Len(r.Value) = 0 Then r.Clear End If Next End Sub -- Gary''s Student - gsnu2007k "Steven B" wrote: Hello all, I have found, in a number of my workbooks, ranges that contain cells that appear to be blank, but excel treats them as though they have data. When I sort a column ascending, these cells end up at the top of my list. Selecting and clearing them removes whatever value is hidden there and they no longer sort to the top of my lists. I'd like to be able to find them and clear all of them in my workbook but I can't figure out how to "find" them. Thoughts? Steven |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear "Blank" cells
Thank you Gary, running it now. Will report back on it's
effectiveness, but it looks like just the thing. Would it execute faster to try something like this: If Len(r.Value) = 0 Then Rng = Union(IIf(Rng Is Nothing, R, Rng), R) End if If Not Rng Is Nothing Then Rng.Clear: Set OLMissing = Nothing I have disable screen updating, calculations and the status bar to help, but it is slogging along. On Jul 28, 11:56*am, Gary''s Student wrote: Try this: Sub cleanup() For Each r In ActiveSheet.UsedRange * * If Len(r.Value) = 0 Then * * * * r.Clear * * End If Next End Sub -- Gary''s Student - gsnu2007k "Steven B" wrote: Hello all, I have found, in a number of my workbooks, ranges that contain cells that appear to be blank, but excel treats them as though they have data. When I sort a column ascending, these cells end up at the top of my list. Selecting and clearing them removes whatever value is hidden there and they no longer sort to the top of my lists. I'd like to be able to find them and clear all of them in my workbook but I can't figure out how to "find" them. Thoughts? Steven |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear "Blank" cells
On Jul 28, 12:25*pm, Steven B wrote:
Thank you Gary, running it now. Will report back on it's effectiveness, but it looks like just the thing. Would it execute faster to try something like this: If Len(r.Value) = 0 Then *Rng = Union(IIf(Rng Is Nothing, R, Rng), R) End if * * * * * * If Not Rng Is Nothing Then Rng.Clear: Set OLMissing = Nothing I have disable screen updating, calculations and the status bar to help, but it is slogging along. On Jul 28, 11:56*am, Gary''s Student wrote: Try this: Sub cleanup() For Each r In ActiveSheet.UsedRange * * If Len(r.Value) = 0 Then * * * * r.Clear * * End If Next End Sub -- Gary''s Student - gsnu2007k "Steven B" wrote: Hello all, I have found, in a number of my workbooks, ranges that contain cells that appear to be blank, but excel treats them as though they have data. When I sort a column ascending, these cells end up at the top of my list. Selecting and clearing them removes whatever value is hidden there and they no longer sort to the top of my lists. I'd like to be able to find them and clear all of them in my workbook but I can't figure out how to "find" them. Thoughts? Steven Correction - ignore the Set OLMissing = Nothing bit, copy/paste from another script I use and didn't edit it properly. S |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear "Blank" cells
Hi Gary et al,
Here is my final code, this worked well and reasonably quickly Sub cleanup() 'Clears "empty cells" in worksheet Dim R As Range Dim Rng As Range Dim i As Long 'integer Dim lrow As Long 'Last Row of data Dim lcol As Long 'last column of data With Application .DisplayAlerts = False CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With lcol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column lrow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).row For i = 1 To lcol Range(Cells(1, i).Address & ":" & Cells(lrow, i).Address).Select For Each R In Selection If Len(R.Value) = 0 Then Set Rng = Union(IIf(Rng Is Nothing, R, Rng), R) End If Next R If Not Rng Is Nothing Then Rng.Clear: Set Rng = Nothing Next i With Application .DisplayAlerts = True .ScreenUpdating = True .Calculation = xlCalculationAutomatic .StatusBar = False End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear "Blank" cells
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 Steven B wrote: Hello all, I have found, in a number of my workbooks, ranges that contain cells that appear to be blank, but excel treats them as though they have data. When I sort a column ascending, these cells end up at the top of my list. Selecting and clearing them removes whatever value is hidden there and they no longer sort to the top of my lists. I'd like to be able to find them and clear all of them in my workbook but I can't figure out how to "find" them. Thoughts? Steven -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear "Blank" cells
Sub Remove_CR_LF()
With Selection ..Replace What:=Chr(39), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(146) & Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(180), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Sub Remove_CR_LF() With Selection ..Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub You have lots of response now. One of these must work... HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Dave Peterson" wrote: 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 Steven B wrote: Hello all, I have found, in a number of my workbooks, ranges that contain cells that appear to be blank, but excel treats them as though they have data. When I sort a column ascending, these cells end up at the top of my list. Selecting and clearing them removes whatever value is hidden there and they no longer sort to the top of my lists. I'd like to be able to find them and clear all of them in my workbook but I can't figure out how to "find" them. Thoughts? Steven -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear "Blank" cells
Hi Dave,
Very cool, thank you. Worked like a charm! Steven |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear "Blank" cells
Hi Ryan,
That one didn't seem to do it. It doesn't see the character in the cell, whether Transition Navigation keys is on or off. I've tried to return the Character number of the ' in the cell, but I can't get Excel to see it. Dave's solution, and my own work though Dave's is much quicker. Thank you for your help, Steven |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to "clear to zero" multiple cells | New Users to Excel | |||
If Cell = "XXX" Then clear all other cells formula | Excel Worksheet Functions | |||
How to count blank cells (Rows) between Non-Blank cells in Col "A" | Excel Discussion (Misc queries) | |||
"Invalid property" after "Clear Form" | Excel Programming | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |