Insert Column Where Cell Value Greater Than Zero

jjaggii

New Member
Joined
Apr 11, 2017
Messages
11
Hi

My spreadsheet has a row of totals where most totals are zero. I have VBA to hide those columns where the totals are zero, but the spreadsheet now looks a bit busy, so I would like to insert a column where the total is greater than zero. So I am looking for VBA code to insert blank columns where the totals are greater than zero. Can anyone suggest code for me please?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I have assumed
a) that we know what row the totals are on. I have used row 20.
b) that totals start in column B
c) that totals will never be negative
d) all columns are visible before the code is run

If all that is so then this should do bot jobs of hiding some columns and inserting some columns.

Test in a copy of your workbook.

Code:
Sub Insert_or_Hide_Columns()
  Dim c As Long
  
  Const TotalRow As Long = 20
  
  For c = Cells(TotalRow, Columns.Count).End(xlToLeft).Column To 2 Step -1
    If Cells(TotalRow, c).Value = 0 Then
      Columns(c).Hidden = True
    Else
      Columns(c + 1).Insert
    End If
  Next c
End Sub
 
Upvote 0
I have assumed
a) that we know what row the totals are on. I have used row 20.
b) that totals start in column B
c) that totals will never be negative
d) all columns are visible before the code is run

If all that is so then this should do bot jobs of hiding some columns and inserting some columns.

Test in a copy of your workbook.

Code:
Sub Insert_or_Hide_Columns()
  Dim c As Long
  
  Const TotalRow As Long = 20
  
  For c = Cells(TotalRow, Columns.Count).End(xlToLeft).Column To 2 Step -1
    If Cells(TotalRow, c).Value = 0 Then
      Columns(c).Hidden = True
    Else
      Columns(c + 1).Insert
    End If
  Next c
End Sub

Hello Peter

Apologies for not replying sooner but I have been floating 3 feet below the surface this week at work. Many thanks for supplying the code. Hopefully I can test it (in a copy as you suggest) at some point today. I will let you know how it goes.
Thanks again,
Ricky
 
Upvote 0
Hello Peter

I will let you know how it goes.
Thanks again,
Ricky
Look forward to hearing how it goes. Just remember to check that all my assumptions are correct, otherwise the suggestion will be invalid.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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
Back
Top