Tom:

I did the test on this code, it only delete when column
is NA, but not column is 0, but it did delete the empty
column.
example:

A B C D E F G
0 NA 0 0 0
0 NA 0 0 0
0 NA 0 0 0

after run this macro, it become like this
A B C D
0 0 0 0
0 0 0 0
0 0 0 0

Lillian
-----Original Message-----

And this will get the blank columns and will speed
things up minimally by
not having to count the number of cells in a column over
and over. Note
that if you have several blank columns at the left most
(such as A and B)
which are not in the UsedRange, these will be left
untouched. This is one
reason I used a different approach, but it is an
unlikely scenario.

Option Explicit
Sub testme03()
Dim myRng As Range
Dim delRng As Range
Dim myCol As Range
Dim wks As Worksheet
Dim cnt as Long

Set wks = Worksheets("sheet1")

With wks
Set myRng = .UsedRange
cnt = myRng.columns(1).Cells.count
For Each myCol In myRng.Columns
If Application.CountIf(myCol, 0) = cnt _
Or Application.CountIf(myCol, "na") = cnt _
Or Application.CountA(myCol) = 0 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

--
Regards,
Tom Ogilvy



Dave Peterson wrote in 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



.