I have a spreadsheet that contains columns with text (such as name, job
title, hire date, etc.) and several columns with amounts. I want to delete
the "amount" columns that contain all zeros. I used the macro below and it
removed all the amount columns that contained just zeros but it also removed
the columns that contained the name, title, etc.

How can I change the macro below to remove only the zero amount columns and
leave the other text columns? Thanks for the assistance.

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
Columns(i).Delete
End If
Next
End Sub

Hi Nora:

Try this modification:

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
i1 = Application.WorksheetFunction.Sum(Columns(i))
i2 = Application.WorksheetFunction.Count(Columns(i))
i3 = Application.WorksheetFunction.CountA(Columns(i))
If i1 = 0 And i2 = i3 Then
Columns(i).Delete
End If
Next
End Sub


You can use a similar technique to test for the existence of text cells in
worksheet formulas.
--
Gary''s Student - gsnu201003


"Nora_GG" wrote:

I have a spreadsheet that contains columns with text (such as name, job
title, hire date, etc.) and several columns with amounts. I want to delete
the "amount" columns that contain all zeros. I used the macro below and it
removed all the amount columns that contained just zeros but it also removed
the columns that contained the name, title, etc.

How can I change the macro below to remove only the zero amount columns and
leave the other text columns? Thanks for the assistance.

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
Columns(i).Delete
End If
Next
End Sub

Thanks again Gary's Student! I additional changes to the changes you
suggested and was able to achieve deleting all amount columns totaling zero
but leaving the columns with text data and dates alone. Really appreciate the
assistance!

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
i1 = Application.WorksheetFunction.Sum(Columns(i))
i2 = Application.WorksheetFunction.Count(Columns(i))
If i1 = 0 And i2 < 0 Then
Columns(i).Delete
End If
Next
End Sub



"Gary''s Student" wrote:

Hi Nora:

Try this modification:

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
i1 = Application.WorksheetFunction.Sum(Columns(i))
i2 = Application.WorksheetFunction.Count(Columns(i))
i3 = Application.WorksheetFunction.CountA(Columns(i))
If i1 = 0 And i2 = i3 Then
Columns(i).Delete
End If
Next
End Sub


You can use a similar technique to test for the existence of text cells in
worksheet formulas.
--
Gary''s Student - gsnu201003


"Nora_GG" wrote:

I have a spreadsheet that contains columns with text (such as name, job
title, hire date, etc.) and several columns with amounts. I want to delete
the "amount" columns that contain all zeros. I used the macro below and it
removed all the amount columns that contained just zeros but it also removed
the columns that contained the name, title, etc.

How can I change the macro below to remove only the zero amount columns and
leave the other text columns? Thanks for the assistance.

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
Columns(i).Delete
End If
Next
End Sub