Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Delete Columns if Empty within a Range

    Greetings,

    I need a macro that deletes entire Column if any column from A to Z is empty,

    So if Column A has Contents anywhere from A1 to the end, then leave it as it is, but if the entire Column B
    is empty, then delete Column B and so on up to Column Z.

    I tried to figure the VBA for this, can do the Row type delete if empty, but the Column if empty is
    doing my head in because it has to be between Columns A - Z.
    I'm not sure if it's loop or what.

    Thanks in Advance.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Try this out. The code will search columns right to left from the bottom of each column and work up until it finds an occupied cell. If it reaches row 1 and that cell is empty, it will delete the column. There can be multiple entries in a column and you can change the 26 to meet the number of columns you wish to check.

    Delcol1.jpg

    Delcol2.jpg

    HTH,
    Maud

    Code:
    Public Sub DeleteCol()
    Application.ScreenUpdating = False
    For I = 26 To 1 Step -1
    Lastrow = ActiveSheet.Cells(Rows.Count, I).End(xlUp).Row
    If Lastrow = 1 And Cells(Lastrow, I) = "" Then ActiveSheet.Columns(I).Delete
    Next I
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-04-08 at 21:17.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Solved !

    Worked a charm,
    Thanks

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    XP,

    Just keep in mind of the cells that your macros and formulas are referencing when deleting columns.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Just keep in mind of the cells that your macros and formulas are referencing when deleting columns.
    Thanks Maud,

    The macro is used to clean up clutter where there is a constant-same-column-same-range of empty space
    after a import.
    But yes, I am aware if there is a slight change from the source, it can mess up the entire process.

    XP

  6. #6
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Maud,

    I am assuming that if I wanted to use this code to hide empty columns rather than delete them, then I would change line 5 of your code to read Then ActiveSheet.Columns(I).Hide

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Hello Maria,

    You would use the following code
    Code:
    Public Sub HideCol()
    Application.ScreenUpdating = False
    For I = 26 To 1 Step -1
    Lastrow = ActiveSheet.Cells(Rows.Count, I).End(xlUp).Row
    If Lastrow = 1 And Cells(Lastrow, I) = "" Then
    ActiveSheet.Columns(I).Select
    Selection.EntireColumn.Hidden = True
    End If
    Next I
    Application.ScreenUpdating = True
    End Sub
    To unhide them, run the same code but subtitute Selection.EntireColumn.Hidden = False

    Code:
    Public Sub HideCol()
    Application.ScreenUpdating = False
    For I = 26 To 1 Step -1
    Lastrow = ActiveSheet.Cells(Rows.Count, I).End(xlUp).Row
    If Lastrow = 1 And Cells(Lastrow, I) = "" Then
    ActiveSheet.Columns(I).Select
    Selection.EntireColumn.Hidden = False
    End If
    Next I
    Application.ScreenUpdating = True
    End Sub
    HTH,
    Maud
    Last edited by Maudibe; 2013-04-15 at 21:35.

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    simmo7 (2013-04-15)

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts

    Automatically find the range of columns to hide

    Maria,
    If you want a dynamic way of finding the last column then you can use the following code to hide the columns. It will get the last column of the used range of the worksheet:
    Code:
    Public Sub DynamicHide()
    Application.ScreenUpdating = False
    ActiveSheet.UsedRange.Select       
    LastCol = Selection.Columns.Count   
    For I = LastCol To 1 Step -1
    Lastrow = ActiveSheet.Cells(Rows.Count, I).End(xlUp).Row
    If Lastrow = 1 And Cells(Lastrow, I) = "" Then
    ActiveSheet.Columns(I).Select
    Selection.EntireColumn.Hidden = True
    End If
    Next I
    Application.ScreenUpdating = True
    End Sub

  10. #9
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Thanks Maud,

    This will be very helpful. Quite often, I need to hide empty columns after I have deleted some content and then unhide them after I have printed out the content I need. I can't simply delete the empty columns as they would still be needed if data needs to be entered for those columns.

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

  11. #10
    New Lounger
    Join Date
    Jun 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi - can this be adjusted so that even if you have column headings in each column, it'll ignore those and delete the column please!?

  12. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    corcoransmith,

    Not sure if I am misunderstanding you but if you are deleting a column and the header value is in that column, where do you want the header to be placed?

    Maud

  13. #12
    Banned Member
    Join Date
    Jun 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It is a good idea to hide the column instead delete because this column again we can need next time.so the above all posted is very useful.Follow above coding the spreadsheet.

  14. #13
    New Lounger
    Join Date
    Jun 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Each of my columns (800) has a column heading, so when I run the macro, it recognizes that there's data in the columns and doesn't remove them! Ideally, I need the macro to run from A2 downwards.. or up to A2, and ignore that there's data in the columns.

    I've kinda gotten around it using your hide functionality by cutting out the headers into a second sheet, then running the macro, and pasting the header row back in -- which then pastes the hidden column headers into the hidden columns..


  15. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Sorry, I thought you wanted to retain the header. A simple adjustment will remove the column if it is empty or if it has a header in row 1

    Code:
    Public Sub DeleteCol()
    Application.ScreenUpdating = False
    For I = 26 To 1 Step -1
    Lastrow = ActiveSheet.Cells(Rows.Count, I).End(xlUp).Row
    If Lastrow = 1 Then ActiveSheet.Columns(I).Delete
    Next I
    Application.ScreenUpdating = True
    End Sub
    HTH,
    Maud

  16. #15
    New Lounger
    Join Date
    Jun 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Sorry, I thought you wanted to retain the header. A simple adjustment will remove the column if it is empty or if it has a header in row 1



    HTH,
    Maud
    THANK YOU, Maude, that's superb!

Posting Permissions

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