Solved

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

Posted on 2014-01-07
7
417 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 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39763746
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
ID: 39763916
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
ID: 39763919
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 81

Expert Comment

by:byundt
ID: 39763924
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
ID: 39763933
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 81

Expert Comment

by:byundt
ID: 39763941
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
ID: 39764041
Excellent..thank you byundt
0

Featured Post

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

831 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