If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I update macro to delete zero amount columns?
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 |
#2
|
|||
|
|||
How can I update macro to delete zero amount columns?
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 |
#3
|
|||
|
|||
How can I update macro to delete zero amount columns?
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 |
Thread Tools | |
Display Modes | |
|
|