no blank cells

E

ekkeindoha

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
 
T

Texas Aggie

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
 
T

Texas Aggie

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
 
T

Texas Aggie

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
 
P

Peo Sjoblom

You could use a filter and filter out blanks and zeros and then copy and
paste the result


--


Regards,


Peo Sjoblom
 
Top