How to delete columns when sum in that columns are zero

br sie

New Member
Joined
Jul 15, 2012
Messages
5
Hi everyone there,

Please kindly advice,
i have thousand of columns, i would like to delete the columns if the sum in that columns are equal to Zero, however i would like to still maintain column 1-10, also column 11, 15, 350, and 500 even the sum in that columns are zero.
could anyone advice the code for this.
Thanks.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
317
Office Version
  1. 2013
Platform
  1. Windows
Hi, br sie,

please copy the following code into a standard mdsule and try it on a copy of the workbook. Please mind it will work on the active sheet and start at column 12 as the first 11 columns should be kept:

Code:
Sub MrExcel_646890()
Dim lngColumn As Long

Application.ScreenUpdating = False
For lngColumn = 12 To ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column
  Select Case lngColumn
    Case 15, 600, 500
      'do nothing
    Case Else
      If WorksheetFunction.Sum(Cells(1, lngColumn).EntireColumn) = 0 Then
        Cells(1, lngColumn).EntireColumn.Value = vbNullString
      End If
  End Select
Next lngColumn
Application.ScreenUpdating = True
End Sub
Ciao,
Holger
 

br sie

New Member
Joined
Jul 15, 2012
Messages
5
Ciao Holger,
Thank You so much for the code, it works (for that column with the sum value ZERO, the zero value in that column deleted, only the column still remain).

How about if i would also like to 'delate' that column (the one with sum is ZERO) as well from the sheet, what code needed to be add?
Thanks to advice.

Ciao,
br sie
 

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
317
Office Version
  1. 2013
Platform
  1. Windows
Hi, br sie,

for any delete start at the end and work up toi the top (reason for this is the counter which then doesn´t need to be corrected when a column is deleted).

Code:
Sub MrExcel_646890_2()
Dim lngColumn As Long

Application.ScreenUpdating = False
For lngColumn = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column To 12 Step -1
  Select Case lngColumn
    Case 15, 350, 500
      'do nothing
    Case Else
      If WorksheetFunction.Sum(Cells(1, lngColumn).EntireColumn) = 0 Then
        Cells(1, lngColumn).EntireColumn.Delete Shift:=xlToLeft
      End If
  End Select
Next lngColumn
Application.ScreenUpdating = True
End Sub
Sorry for the 600 - it should read 350.

Ciao,
Holger
 

Watch MrExcel Video

Forum statistics

Threads
1,122,566
Messages
5,596,898
Members
414,109
Latest member
Coops01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top