DELETE COLUMNS ONLY IF THERE IS NO DATA IN THE WHOLE COLUMN
Oops. I didn't read the rest of your routine. I'm sure the OP is glad you
posted your correction.
But I still like <= 1 (just in case there is a completely empty column).
Norman Jones wrote:
Hi Juan,
Dave's suggestion of changing
If Application.CountA(col) = 0 Then
to
If Application.CountA(col) = 1 Then
is a good suggestion.
Unfortunately, my original code is flawed: when deleteting columns (or rows)
like this, deletion should start at the last column and work backwards.
So try this version:
Sub Tester02A()
Dim col As Range
Dim sh As Worksheet
Dim Rng As Range, rng1 As Range
Dim i As Long
Set sh = ActiveSheet '<<===== CHANGE
For i = sh.UsedRange.Columns.Count To 1 Step -1
If Application.CountA(Columns(i)) = 1 Then
Columns(i).EntireColumn.Delete
End If
' End If
Next i
End Sub
---
Regards,
Norman
"Juan" wrote in message
...
Hello Norman,
thanks for the quick reply. Actually, I need for it to look after row 1.
Your macro does work if I delete data on row 1. Becasuse Row 1 is my
headers.
So the macro will not delete because there will always be a header.
Hope this is clear sorry didn't explain it early.
Please advise how to modify to do this.
Thanks,
Juan
"Norman Jones" wrote:
Hi Juan,
Sorry, misread hide for delete.
To delete empty columns,try:
Sub Tester02()
Dim col As Range
Dim sh As Worksheet
Set sh = ActiveSheet '<<===== CHANGE
For Each col In sh.UsedRange.Columns
If Application.CountA(col) = 0 Then
col.EntireColumn.Delete
End If
Next col
End Sub
---
Regards,
Norman
"Juan" wrote in message
...
Hi,
need to delete any column only if there is no Data in the whole column
Sample data i Have:
PART# Apr03 Apr04 Apr05 Aug03 Aug04
Dec03
ZDTD1 0.0247 2.5 2.4
KEF4 0.1349 538 2.6
So my end result should be:
PART# Apr03 Apr05 Aug03 Dec03
ZDTD1 0.0247 2.5 2.4
KEF4 0.1349 538 2.6
Please advise. Appreciate any feedback/help
Thanks,
Juan
--
Dave Peterson
|