delete empty column in VBA
Tom:
I did try this macro, it's cool, it delete if any
columns has value 0, or empty, it will be delete then move
to the left, that's great.
How about if I want to delete any column has
value "NA", can we included in this macro?
in this macro I have question?
what is rng and rng1, is rng is for column, rng1 is for
row, in your code, you said:
1). For i =rng.Column to 1 Step -1
what you use -1?
2).Can you explain line by line on your code, I am confuse
try to understand your macro.
I really appreciated it.
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
.
|