• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

How do I delete several columns if the sum of the column is 0

Note I want to via VBA ananlyze each column in a worksheet and if the sum is 0 then delete the column.  Note I can not use a static helper row to sum the column as the number of my rows will potentially change each time I run the spreadsheet...

In the attached I would need to have the macro delete Column "I"

I know I need to figure out the last row which I did...but from here I am a little unsure on how to proceed

Last Row = Range ("A" & Rows.Count).End(xlUp).Row
ee-del-col.xlsm
0
upobDaPlaya
Asked:
upobDaPlaya
  • 4
  • 3
1 Solution
 
byundtCommented:
You don't need to know the last row to delete columns with a sum of zero. Just use the UsedRange property of the worksheet and test the sum of its columns.

Sub DeleteZeroSumColumns()
Dim rg As Range
Dim i As Long
Application.ScreenUpdating = False
Set rg = ActiveSheet.UsedRange
For i = rg.Columns.Count To 1 Step -1       'Change 1 to 2 if column A is header labels
    If Application.Sum(rg.Columns(i)) = 0 Then rg.Columns(i).EntireColumn.Delete
Next
End Sub

Open in new window

0
 
upobDaPlayaAuthor Commented:
Very Slick and compact !  Exactly what I needed.  I am still struggling a little bit with the object model, thus the reason for my next question.  Why is it Application.Sum ?
0
 
upobDaPlayaAuthor Commented:
Excellent solution.  I had researched my question on the web for a while as I always like to try it on my own, but all the solutions I saw were for deleting rows.  I like how this solution is low overhead and of course answers my question.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
byundtCommented:
I could have used Application.WorksheetFunction.Sum instead--and you might have found that approach in Microsoft's help. But I have observed run-time errors with certain worksheet functions (such as MATCH) if they return an error value. I can avoid these run-time errors by using Application.Match, and so have adopted that as a standard practice. I apologize if it confused you.

Brad
0
 
upobDaPlayaAuthor Commented:
Thanks byundt for the explanation.  High Level what is the difference between the 2 (Application.WorksheetFunction.Sum and Application.Sum) from Excel's brain perspective.  Sorry if this is digging in the weeds, but it would be great to end my question on this nugget of knowledge.  thanks !
0
 
byundtCommented:
Both approaches use Excel's SUM function to return a result.

Application.WorksheetFunction.Sum (or WorksheetFunction.Sum without the preceding Application) will give you a bit of IntelliSense regarding the required parameters. Application.Sum expects you to know those parameters.

Application.Sum will avoid a run-time error should the values being summed contain an error value. I've been bitten by the run-time error on a number of occasions when using Application.WorksheetFunction.name of function because the WorksheetFunction property of the Application object cannot equal an error value. For this reason, I always use Application.name of function instead.
0
 
upobDaPlayaAuthor Commented:
Excellent..thank you byundt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now