Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
no blank cells
Need to know a formula to remove blank cells or cells containing 0.
eg A 21 0 33 44 0 What I'm looking for are A 21 33 44 No zero or blank cells. thx |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
no blank cells
There are two ways that I know of to take away the blank cells, I dont know
about the zeros... Manually you can do this by Edit Go To Special Blanks Edit Delete Shift Entire Row That is starting on A1 There is a code as well Option Explicit Sub testme01() Dim myRng As Range With ActiveSheet Set myRng = Nothing On Error Resume Next Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If myRng Is Nothing Then MsgBox "no blanks in column A" Exit Sub End If Intersect(myRng.EntireRow, .Columns("A:P")) _ .Cells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp End With End Sub test it, select the column and play that macro -- If this reply was helpful, please indicate that your question has been answered to help others find anwsers to similar questions. www.silverbirddesigns.com Fighting Texas Aggie Class of 2009 "ekkeindoha" wrote: Need to know a formula to remove blank cells or cells containing 0. eg A 21 0 33 44 0 What I'm looking for are A 21 33 44 No zero or blank cells. thx |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
no blank cells
If you want to permanently delete them, sort the data and delete all the
rows with '0' If you want to hide them temporarily, check out DataFilterAutofilter -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk UK Excel User Conference In Cambridge November 28th - December 1st 2007 Register Now @ http://www.exceluserconference.com/UKEUC.html "ekkeindoha" wrote in message ... Need to know a formula to remove blank cells or cells containing 0. eg A 21 0 33 44 0 What I'm looking for are A 21 33 44 No zero or blank cells. thx |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
no blank cells
If you want to delete the Blank Cells and Zeros, here is the complete code,
asuming that your data is in column A Option Explicit Sub testme01() Dim rng As Range, cell As Range Dim rng1 As Range On Error Resume Next Set rng = ActiveSheet.UsedRange.SpecialCells(xlConstants, xlNumbers) Set rng1 = ActiveSheet.UsedRange.SpecialCells(xlFormulas, xlNumbers) On Error GoTo 0 If Not rng Is Nothing Then For Each cell In rng If cell.Value = 0 Then cell.ClearContents Next End If If Not rng1 Is Nothing Then For Each cell In rng1 If cell.Value = 0 Then cell.ClearContents Next End If Dim myRng As Range With ActiveSheet Set myRng = Nothing On Error Resume Next Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If myRng Is Nothing Then MsgBox "no blanks in column A" Exit Sub End If Intersect(myRng.EntireRow, .Columns("A:P")) _ .Cells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp End With End Sub -- If this reply was helpful, please indicate that your question has been answered to help others find anwsers to similar questions. www.silverbirddesigns.com Fighting Texas Aggie Class of 2009 "ekkeindoha" wrote: Need to know a formula to remove blank cells or cells containing 0. eg A 21 0 33 44 0 What I'm looking for are A 21 33 44 No zero or blank cells. thx |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
no blank cells
Nick,
If you sort them and delete, how do you plan on getting them back to the order in which they were in? Sorry, just curious. -- If this reply was helpful, please indicate that your question has been answered to help others find anwsers to similar questions. www.silverbirddesigns.com Fighting Texas Aggie Class of 2009 "Nick Hodge" wrote: If you want to permanently delete them, sort the data and delete all the rows with '0' If you want to hide them temporarily, check out DataFilterAutofilter -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk UK Excel User Conference In Cambridge November 28th - December 1st 2007 Register Now @ http://www.exceluserconference.com/UKEUC.html "ekkeindoha" wrote in message ... Need to know a formula to remove blank cells or cells containing 0. eg A 21 0 33 44 0 What I'm looking for are A 21 33 44 No zero or blank cells. thx |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
no blank cells
You could use a filter and filter out blanks and zeros and then copy and
paste the result -- Regards, Peo Sjoblom "Texas Aggie" wrote in message ... Nick, If you sort them and delete, how do you plan on getting them back to the order in which they were in? Sorry, just curious. -- If this reply was helpful, please indicate that your question has been answered to help others find anwsers to similar questions. www.silverbirddesigns.com Fighting Texas Aggie Class of 2009 "Nick Hodge" wrote: If you want to permanently delete them, sort the data and delete all the rows with '0' If you want to hide them temporarily, check out DataFilterAutofilter -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk UK Excel User Conference In Cambridge November 28th - December 1st 2007 Register Now @ http://www.exceluserconference.com/UKEUC.html "ekkeindoha" wrote in message ... Need to know a formula to remove blank cells or cells containing 0. eg A 21 0 33 44 0 What I'm looking for are A 21 33 44 No zero or blank cells. thx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
How to count blank cells (Rows) between Non-Blank cells in Col "A" | Excel Discussion (Misc queries) | |||
Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
Making Blank Cells Really Blank (Zen Koan) | Excel Worksheet Functions |