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

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

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

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

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

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