Tom:

Let me give an example as following:

A B C D E F G H

0 NA 0 123
0 NA 0 222
0 NA 0 333
0 NA 0 444
0 NA 0 555

If you see entire ColumnA is "0", entire ColumnC is "NA"
entire ColumnD is "0", how to write the macro to delete
entire columnA,C,D, not just delete value, also column
need to delete as well, otherwise leave a lots of empty
column in between of columns.
also on this example Column E,F,G is empty column, how
to deleted the empty column then H column will be move to
left, I mean is all the entire spreed sheet data is from
ColumnA to 30 columns to the right need moved close
together, I don't want to scrool data to the right to see
entire row.

thank you for all the help.

Lillian
-----Original Message-----
I assume if a column has only Errors, 0, blank cells or a
combination, then
delete.

Sub AAA1()
Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim cnt As Long, cnt1 As Long
Dim i As Long
With ActiveSheet
cnt = .UsedRange.Rows.Count
Set rng = .UsedRange.Columns
(.UsedRange.Columns.Count).Cells(1, 1)
For i = rng.Column To 1 Step -1
Set rng1 = Intersect(.Columns
(i), .UsedRange.EntireRow).Cells
If Application.CountA(rng1) = 0 Then
rng1.EntireColumn.Delete
Else
cnt1 = 0
For Each cell In rng1
If IsNumeric(cell.Value) Then
If cell.Value = 0 Then
cnt1 = cnt1 + 1
End If
ElseIf IsError(cell) Then
cnt1 = cnt1 + 1
ElseIf IsEmpty(cell) Then
cnt1 = cnt1 + 1
End If
Next
If cnt = cnt1 Then _
rng1.EntireColumn.Delete
End If
Next
End With
End Sub

--
Regards,
Tom Ogilvy



Lillian wrote in
message
...
I have excel spreed sheet that I have some empty column
in between the columns, is anyway write the macro to do
that? also if all entire column has "0" and "NA", can I
deleted them all as well.

thanks.


Lillian


.