Results 1 to 6 of 6

Thread: Insert Column Where Cell Value Greater Than Zero

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert Column Where Cell Value Greater Than Zero

    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?

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Insert Column Where Cell Value Greater Than Zero

    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Apr 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert Column Where Cell Value Greater Than Zero

    Quote Originally Posted by Peter_SSs View Post
    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

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Insert Column Where Cell Value Greater Than Zero

    Quote Originally Posted by jjaggii View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member
    Join Date
    Apr 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert Column Where Cell Value Greater Than Zero

    Many many thanks, Peter. That worked beautifully

    Ricky

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Insert Column Where Cell Value Greater Than Zero

    Quote Originally Posted by jjaggii View Post
    Many many thanks, Peter. That worked beautifully

    Ricky
    You are very welcome. Thanks for the follow-up.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •