Solved

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

Posted on 2014-01-07
7
383 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
0
Comment
Question by:upobDaPlaya
  • 4
  • 3
7 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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
 

Author Comment

by:upobDaPlaya
Comment Utility
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
 

Author Closing Comment

by:upobDaPlaya
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
 

Author Comment

by:upobDaPlaya
Comment Utility
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
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
 

Author Comment

by:upobDaPlaya
Comment Utility
Excellent..thank you byundt
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now