Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETE COLUMNS ONLY IF THERE IS NO DATA IN THE WHOLE COLUMN
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETE COLUMNS ONLY IF THERE IS NO DATA IN THE WHOLE COLUMN
Hi Juan,
Try something like: Sub Tester01() Dim col As Range Dim sh As Worksheet Set sh = ActiveSheet '<<===== CHANGE For Each col In sh.UsedRange.Columns col.EntireColumn.Hidden = Application.CountA(col) = 0 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETE COLUMNS ONLY IF THERE IS NO DATA IN THE WHOLE COLUMN
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETE COLUMNS ONLY IF THERE IS NO DATA IN THE WHOLE COLUMN
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETE COLUMNS ONLY IF THERE IS NO DATA IN THE WHOLE COLUMN
If every column has a header in row 1, you could just change this line:
If Application.CountA(col) = 0 then to If Application.CountA(col) <= 1 then Juan wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETE COLUMNS ONLY IF THERE IS NO DATA IN THE WHOLE COLUMN
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETE COLUMNS ONLY IF THERE IS NO DATA IN THE WHOLE COLUMN
Hello Norman,
thank you. It seems to work. Will do more testing just in case but seems it shoudl be fine. Will repost if I see anythign wrong. oNCE Again thanks for the quick reply. Juan "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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETE COLUMNS ONLY IF THERE IS NO DATA IN THE WHOLE COLUMN
Dave,
thanks also for your feedback. appreciate all the help. juan "Dave Peterson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete columns if cells have no data | Excel Discussion (Misc queries) | |||
Proper procedures to delete multiple columns with 47,000 rows of data | Excel Discussion (Misc queries) | |||
How to delete duplicates based on data in 2 columns? | Excel Discussion (Misc queries) | |||
VBA Delete row based on multiple columns of data | Excel Discussion (Misc queries) | |||
Comparing data in columns and delete if not equal | Excel Programming |