Tom:

This one even better, I really appreciated all your
effort.

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



.