ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   DELETE COLUMNS ONLY IF THERE IS NO DATA IN THE WHOLE COLUMN (https://www.excelbanter.com/excel-programming/335701-delete-columns-only-if-there-no-data-whole-column.html)

Juan
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

Norman Jones
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

Norman Jones
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

Juan
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

Dave Peterson
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

Norman Jones
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

Dave Peterson
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

Juan
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

Juan
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


All times are GMT +1. The time now is 08:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com