A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Remove columns with all zeros



 
 
Thread Tools Display Modes
  #11  
Old June 4th, 2010, 12:46 AM posted to microsoft.public.excel.misc
Nora_GG[_2_]
external usenet poster
 
Posts: 10
Default Remove columns with all zeros

My apologies Gord Dibben.

"Gord Dibben" wrote:

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

On Thu, 3 Jun 2010 10:13:51 -0700, Nora_GG
wrote:

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


"Jacob Skaria" wrote:

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.

--
Jacob (MVP - Excel)


"Nora_GG" wrote:

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.

"Jacob Skaria" wrote:

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


--
Jacob (MVP - Excel)


"Nora_GG" wrote:

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


.

  #12  
Old June 4th, 2010, 12:49 AM posted to microsoft.public.excel.misc
Nora_GG[_2_]
external usenet poster
 
Posts: 10
Default Remove columns with all zeros

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



"Jacob Skaria" wrote:

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.

--
Jacob (MVP - Excel)


"Nora_GG" wrote:

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.

"Jacob Skaria" wrote:

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


--
Jacob (MVP - Excel)


"Nora_GG" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.