Remove columns with all zeros

  • Thread starter Thread starter Nora_GG
  • Start date Start date
N

Nora_GG

How can I remove columns that contain all zeros? Is there a macro? I craeted
a sumif formula to flag columns subtotaling zero but I need a method to
remove these columns. Thanks
 
Try this:

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
Columns(i).Delete
End If
Next
End Sub
 
I would use COUNTIF() instead..

Sub DeleteColumnswithZeros()
Dim lngCol As Long, lngLastCol As Long

lngLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For lngCol = lngLastCol To 1 Step -1
If WorksheetFunction.CountIf(Columns(lngCol), 0) + _
WorksheetFunction.CountBlank(Columns(lngCol)) = _
Rows.Count Then Columns(lngCol).Delete
Next
End Sub
 
Thank you Gary's Student. I am pretty new to building macros. I created the
macro below in a separate workbook and then opened it in the workbook I
needed to apply the macro. Should I have created the macro in the workbook
that needed the columns removed? Also, did I need to include a range in the
macro below or was it ok to copy as is? Appreciate the assistance.
 
Thank you Jacob. I am pretty new to building macros. I created the macro
below in a separate workbook and then opened it in the workbook I needed to
apply the macro. Should I have created the macro in the workbook that needed
the columns removed? Also, did I need to include a range in the macro below
or was it ok to copy as is? Appreciate the assistance.

By the way, I created the macro below by selecting the Macro option under
Tools.

Thanks again.
 
For a start you would be best off copying and pasting the macro as is to a
general module in the workbook to acted upon.

Otherwise you would have to qualify which workbook has the ActiveSheet.

UsedRange should take care of all columns and rows that Excel sees as being
in use on the active worksheet.


Gord Dibben MS Excel MVP
 
Hello. I set up teh macro and it removed teh columns with all zeros. It also
removed columns containing text data. How can I tweak the macro below so it
ignores columns that contain data such as names, job title, etc.

Thanks again for your asisstance.
 
If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


--I created the macro below in a separate workbook and then opened it in the
workbook I needed to apply the macro. Should I have created the macro in the
workbook that needed the columns removed?

No need; the workbook in which you need to delete the columns should be the
active workbook..

--Also, did I need to include a range in the macro below or was it ok to
copy as is? Appreciate the assistance.

No need; the macro identifies the last column with values and check for zero
cells upto that column.
 
Thanks again Jacob. I applied the macro below and it didn't do anything. I
also applied the macro Gord Dibben suggested and it removed all columns
totaling zero. The only problem is that it also removed columns containing
text such as Names, Job Title, etc.

Can you suggest how I can tweak the macro below so it disregards columns
with text and dates and looks at only the numbers?

Again really apperciate the assistance.


Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
Columns(i).Delete
End If
Next
End Sub
 
Gord Dibben never suggested a macro.

Gord told you where to store the macro that Gary's Student had posted for
you.


Gord Dibben MS Excel MVP
 
My apologies Gord Dibben.

Gord Dibben said:
Gord Dibben never suggested a macro.

Gord told you where to store the macro that Gary's Student had posted for
you.


Gord Dibben MS Excel MVP



.
Click to expand...
 
Hi Jacob, I received additional assistance from Gary's Student and was able
to tweak the macro to provide me with the desired results. Thank you again.

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
i1 = Application.WorksheetFunction.Sum(Columns(i))
i2 = Application.WorksheetFunction.Count(Columns(i))
If i1 = 0 And i2 <> 0 Then
Columns(i).Delete
End If
Next
End Sub
 
Back
Top