• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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