using Macro in excel spreed sheet
Dave:
I test this macro and it only delete the NA but not 0,
any idea? example
A B C D E
0 NA 0 NA 0
0 NA 0 NA 0
0 NA 0 NA 0
after that 0 still there, thank you.
Lillian
-----Original Message-----
I read your other post and this'll get both 0's and NA's.
Option Explicit
Sub testme03()
Dim myRng As Range
Dim delRng As Range
Dim myCol As Range
Dim wks As Worksheet
Set wks = Worksheets("sheet1")
With wks
Set myRng = .UsedRange
For Each myCol In myRng.Columns
If Application.CountIf(myCol, 0) =
myCol.Cells.Count _
Or Application.CountIf(myCol, "na") =
myCol.Cells.Count Then
If delRng Is Nothing Then
Set delRng = myCol.Cells(1)
Else
Set delRng = Union(myCol.Cells(1),
delRng)
End If
End If
Next myCol
End With
If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End Sub
<<snipped
--
Dave Peterson
.
|