We help IT Professionals succeed at work.

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

upobDaPlaya
upobDaPlaya asked
on
864 Views
Last Modified: 2014-01-07
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
Comment
Watch Question

Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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 ?

Author

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.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Author

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 !
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.

Author

Commented:
Excellent..thank you byundt

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.