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

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

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

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

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

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

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

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

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