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