Insert Column Where Cell Value Greater Than Zero

[jjaggii]

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?

 

[Peter_SSs]

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.

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

 

[jjaggii]

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.

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

 

[Peter_SSs]

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.

 

[jjaggii]

Many many thanks, Peter. That worked beautifully

Ricky

 

[Peter_SSs]

Many many thanks, Peter. That worked beautifully

Ricky

You are very welcome. Thanks for the follow-up.