+ Reply to Thread
Results 1 to 2 of 2

How to delete entire column if it is Empty

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-28-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    115

    How to delete entire column if it is Empty

    I have columns
    Row 1 is heading..


    IF Column C doesn't have data in entire column then delete C D E F
    IF Column D doesn't have data in entire column then delete D E F
    IF Column E doesn't have data in entire column then delete E F
    IF Column F doesn't have data in entire column then delete F


    Same way for heading NN's

    IF Column G doesn't have data in entire column then delete G H I J K L M N
    IF Column H doesn't have data in entire column then delete H I J K L M N
    IF Column I doesn't have data in entire column then delete I J K L M N
    IF Column J doesn't have data in entire column then delete J K L M N
    IF Column K doesn't have data in entire column then delete K L M N
    IF Column L doesn't have data in entire column then delete L M N
    IF Column M doesn't have data in entire column then delete M N
    IF Column N doesn't have data in entire column then delete N

    A B C D E F G H I J K L M N O P Q R
    TITLE1 TITLE2 TT1 TT2 TT3 TT4 NN1 NN2 NN3 NN4 NN5 NN6 NN7 NN8 CMT K2 K3
    BLAH BLAH


    Thanks
    Last edited by zit1343; 02-21-2013 at 11:11 AM.

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: How to delete entire column if it is Empty

    this macro will delete column C, D, E and/or F depending on columns conten:
    Public Sub test()
    Dim C_ol As Range
    For Each C_ol In Range("C1:F1")
      If C_ol.EntireColumn.Cells.SpecialCells(xlCellTypeConstants).Count - 1 = 0 Then
        Range(C_ol, Range("F1")).EntireColumn.Delete
        Exit For
      End If
    Next
    End Sub
    This one will delete columns G, H, I J K L M and/or N depending on columns contents:
    Public Sub test()
    Dim C_ol As Range
    For Each C_ol In Range("G1:N1")
      If C_ol.EntireColumn.Cells.SpecialCells(xlCellTypeConstants).Count - 1 = 0 Then
        Range(C_ol, Range("N1")).EntireColumn.Delete
        Exit For
      End If
    Next
    End Sub
    The problem here is that when you run the first macro, the OLD column G is no longer at G because you'll have deleted some columns. So you can run either one of them but not both unless we can refer to titles to locate the columns to be deleted.
    Hope this helps.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1