# delete columns vba - but ignore 1st row



## John Caines (Dec 23, 2022)

Hello All.
I'm trying to find a vba solution to delete all empty (selected range) columns in a spreadsheet,,,but with a twist.
The spreadsheet columns have a header in Row A,,,so technically the column isn't completely empty.

So it's trying to write some vba code to delete all empty columns that have a header (in row A),,, and if nothing is in the column below A, delete the row.
I did find a vba code on the www. here;








						How to remove blank columns in Excel
					

See how to delete blank columns in Excel with a macro, formula or a button-click.




					www.ablebits.com
				




Code was;

```
Public Sub DeleteEmptyColumns()
    Dim SourceRange As Range
    Dim EntireColumn As Range

    On Error Resume Next

    Set SourceRange = Application.InputBox( _
        "Select a range:", "Delete Empty Columns", _
        Application.Selection.Address, Type:=8)

    If Not (SourceRange Is Nothing) Then
        Application.ScreenUpdating = False

        For i = SourceRange.Columns.Count To 1 Step -1
            Set EntireColumn = SourceRange.Cells(1, i).EntireColumn
            If Application.WorksheetFunction.CountA(EntireColumn) = 0 Then
                EntireColumn.Delete
            End If
        Next

        Application.ScreenUpdating = True
    End If
End Sub
```

But the above code doesn't take into account the headings in row A.

Is there anyway to modify this code above so I can delete empty columns on a range I select (taking into account the selected range, you'd be ignoring the Row A

Hope the above makes sense and someone can help me out here.

Best regards
John C


----------



## johnnyL (Dec 23, 2022)

Change the zero to 1


```
If Application.WorksheetFunction.CountA(EntireColumn) = 1 Then
```


----------



## John Caines (Dec 23, 2022)

Hello JohnnyL!
Many thanks for your reply.
I just tried the above formula with your suggestion, but it didn't work 
The first ROW (Row 1) that has the headings has comments in, but this shouldn't affect it from running correctly.
mr-excl-example-sheet.xlsxABCDEFGHI1Country  –  DNS  :  HostCountry ISO Code  –  DNS  :  HostContinent  –  DNS  :  HostLinking C Blocks  –  Moz  :  Root DomainLinks  –  Moz  :  Root DomainExternal Links  –  Moz  :  Root DomainExternal Equity-Passing Links  –  Moz  :  Root DomainRoot Domains Linking  –  Moz  :  Root DomainTime Last Crawled  –  Moz  :  URL2United StatesUSNorth America10/12/2018 1:003United StatesUSNorth America10/15/2018 1:004United StatesUSNorth America10/10/2018 1:005IndiaINAsia10/12/2018 1:006GermanyDEEurope11/7/2018 0:007IndiaINAsia10/11/2018 1:00812/19/2018 0:009FranceFREurope12/12/2018 0:0010United StatesUSNorth America10/1/2018 1:0011United StatesUSNorth America10/10/2018 1:0012GermanyDEEurope10/15/2018 1:0013CanadaCANorth America10/3/2018 1:0014United StatesUSNorth America10/6/2018 1:0015United StatesUSNorth America8/4/2017 1:0016United StatesUSNorth America8/24/2018 1:0017CyprusCYEurope11/15/2018 0:0018SingaporeSGAsia10/5/2018 1:0019United StatesUSNorth America10/14/2018 1:0020United StatesUSNorth America10/13/2018 1:0021United StatesUSNorth America10/5/2018 1:0022BulgariaBGEurope11/2/2018 0:0023IndiaINAsia10/22/2018 1:0024CanadaCANorth America10/8/2018 1:0025United StatesUSNorth America10/17/2018 1:0026United KingdomGBEurope11/3/2018 0:0027United StatesUSNorth America10/9/2018 1:00289/22/2018 1:0029United StatesUSNorth America10/2/2018 1:0030United StatesUSNorth America10/15/2018 1:0031FranceFREurope1/15/2017 0:0032SingaporeSGAsia8/10/2018 1:0033BahrainBHAsia11/24/2018 0:0034United StatesUSNorth America11/17/2018 0:0035IndiaINAsia12/12/2018 0:0036United KingdomGBEurope7/2/2018 1:0037United StatesUSNorth America10/10/2018 1:0038GermanyDEEurope10/13/2018 1:003910/17/2018 1:0040United StatesUSNorth America10/21/2018 1:0041United StatesUSNorth America12/17/2018 0:0042United StatesUSNorth America10/27/2018 1:0043United StatesUSNorth America11/12/2018 0:0044NetherlandsNLEurope11/7/2018 0:0045United StatesUSNorth America8/6/2018 1:0046United StatesUSNorth America10/9/2018 1:0047United StatesUSNorth America11/30/2017 0:0048United StatesUSNorth America10/4/2018 1:004910/22/2018 1:0050United StatesUSNorth America12/3/2018 0:0051United StatesUSNorth America11/1/2018 0:00529/27/2018 1:0053United StatesUSNorth America12/11/2018 0:00545/28/2018 1:0055United StatesUSNorth America10/27/2018 1:0056United StatesUSNorth America6/19/2017 1:0057SingaporeSGAsia9/30/2018 1:00Sheet1

I've just used Xl2bb to show you a sample,, where the macro/vba should delete columns D,E,F,G,H, but it doesn't JohnnyL

Any ideas why not?
Love to get this working.
Many thanks again for your reply

Best regards

John C


----------



## johnnyL (Dec 23, 2022)

Just tried it on your sample & it worked for me.


```
Public Sub DeleteEmptyColumns()
    Dim SourceRange As Range
    Dim EntireColumn As Range

    On Error Resume Next

    Set SourceRange = Application.InputBox( _
        "Select a range:", "Delete Empty Columns", _
        Application.Selection.Address, Type:=8)

    If Not (SourceRange Is Nothing) Then
        Application.ScreenUpdating = False

        For i = SourceRange.Columns.Count To 1 Step -1
            Set EntireColumn = SourceRange.Cells(1, i).EntireColumn
            If Application.WorksheetFunction.CountA(EntireColumn) = 1 Then
                EntireColumn.Delete
            End If
        Next

        Application.ScreenUpdating = True
    End If
End Sub
```


----------



## John Caines (Dec 23, 2022)

Hi JohnnyL

This is driving me mad!
I've just created a short screencam of what I have and it not working for me here
(It's a mp4 video I've just uploaded to my dropbox JohnnyL
video-for-johnnyl

If you please could just see if I'm doing something stupid here JohnnyL, I just can't get it to delete columns  
Hope you can advise

Best regards
A very confused...
John C


----------



## johnnyL (Dec 23, 2022)

When you run the script and it asks for the range, you are selecting the entire range.

Just select the columns you want Example A1:H1 for example.


----------



## John Caines (Dec 23, 2022)

Hi JohnnyL,
It's still not working for me! 
I include image JohnnyL.

Even though this is a sample sheet,,, the actual sheet may have like 100 columns, all with headers in row 1,,,I just want to be able to click a button and it deletes all the columns with no data.
I'm really not sure why this isn't working JohnnyL
here's another video, just to show you.
still-not-working-video-for-johnnyl

I really don't know what the issue is here Johnny.
Am I still doing it wrong in this video?
I can't be??

Cheers JohnnyL
Has to work soon! LOL
John C


----------



## johnnyL (Dec 23, 2022)

You did the same thing, effectively, as what you did in the last video. Perhaps that was because of some poor wording in my last response.

Let me try again. 

Don't select the column letters. You should be selecting cells on Row 1. Example A1:H1


----------



## Peter_SSs (Dec 23, 2022)

John Caines said:


> I really don't know what the issue is here


I believe that it relates to your data and the fact that columns you think have nothing in them other than a header actually do have something else. At one point in your post #7 video you selected column D by clicking its heading label.
At that point I paused your video and although it is very hard to read, I believe that at the right of the status bar it says something like Count=37 indicating there is more than just a heading in that column.

Here is another code to try. See what happens with it.


```
Sub Del_Cols()
  Dim c As Long, rw As Long
 
  Application.ScreenUpdating = False
  For c = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
    rw = Columns(c).Find(What:="?*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    If rw = 1 Then Columns(c).Delete
  Next c
  Application.ScreenUpdating = True
End Sub
```


----------



## John Caines (Dec 24, 2022)

Hi JohnnyL & Peter_SSs!
Many many thanks for both your replies.

1st of all, I'll upload my macro enabled workbook with the example data in (I have used in screencams below, )
example-workbook
Sorry JohnnyL, I just can't get it to work at all.
If you say exactly what cell range to select I'll try it again (IE A1:I1,,, or if it's $A1:$I1,,, whatever you say it is, I'll try it again,,), but I've tried a million combinations now! (a bit of an exaggeration, but it feels like it!) ... but maybe still not the right 1!

I think Peter_SSs might be onto something, as if I select for example (highlight) COL D, it shows a count in lower left of workbook of 57, even though nothing is in COL D below Row 1,, very strange?
Image attached.
Really not sure what is wrong here.

But moving on,,,many thanks again Peter_Sss for taking a look also and uploading your code.
I just tested it, and it worked!
It deleted the columns in sample sheet D:H

I just tried it in a bigger sheet with data in Columns A:FX
and it worked again! Brilliant!  
This is a huge huge timesaver for me Peter_SSs

Is there anyway to tweak it just slightly?
It would be great for it to say after it has run something like,

Starting Column Count: 180
Deleted Columns: 49
Finishing Column Count: 131

This would be handy to know.
But I really grateful Peter_SSs for this, as I said, it worked 1st time, brilliant.

Again, sorry I couldn't get the 1st VBA code to run JohnnyL, I just don't know why it won't work for me.

I appreciate very much your replies.

Have a great day
Best regards
John C


----------



## John Caines (Dec 23, 2022)

Hello All.
I'm trying to find a vba solution to delete all empty (selected range) columns in a spreadsheet,,,but with a twist.
The spreadsheet columns have a header in Row A,,,so technically the column isn't completely empty.

So it's trying to write some vba code to delete all empty columns that have a header (in row A),,, and if nothing is in the column below A, delete the row.
I did find a vba code on the www. here;








						How to remove blank columns in Excel
					

See how to delete blank columns in Excel with a macro, formula or a button-click.




					www.ablebits.com
				




Code was;

```
Public Sub DeleteEmptyColumns()
    Dim SourceRange As Range
    Dim EntireColumn As Range

    On Error Resume Next

    Set SourceRange = Application.InputBox( _
        "Select a range:", "Delete Empty Columns", _
        Application.Selection.Address, Type:=8)

    If Not (SourceRange Is Nothing) Then
        Application.ScreenUpdating = False

        For i = SourceRange.Columns.Count To 1 Step -1
            Set EntireColumn = SourceRange.Cells(1, i).EntireColumn
            If Application.WorksheetFunction.CountA(EntireColumn) = 0 Then
                EntireColumn.Delete
            End If
        Next

        Application.ScreenUpdating = True
    End If
End Sub
```

But the above code doesn't take into account the headings in row A.

Is there anyway to modify this code above so I can delete empty columns on a range I select (taking into account the selected range, you'd be ignoring the Row A

Hope the above makes sense and someone can help me out here.

Best regards
John C


----------



## Peter_SSs (Dec 24, 2022)

John Caines said:


> I just don't know why it won't work for me.


It won't work for anybody *if they are using your data* because when it counts the cells with values in the columns it does not get 1. Your 'empty' cells actually contain zero-length strings and so are counted by
`Application.WorksheetFunction.CountA(EntireColumn)`

In a vacant cell to the right put this formula
=ISTEXT(D2)

In another vacant cell to the right put this formula
=ISTEXT(P20)

Compare the results.



John Caines said:


> Is there anyway to tweak it just slightly?


Try this


```
Sub Del_Cols_v2()
  Dim c As Long, rw As Long, TotCols As Long, DelCols As Long
 
  Application.ScreenUpdating = False
  TotCols = Cells(1, Columns.Count).End(xlToLeft).Column
  For c = TotCols To 1 Step -1
    rw = Columns(c).Find(What:="?*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    If rw = 1 Then
      DelCols = DelCols + 1
      Columns(c).Delete
    End If
  Next c
  Application.ScreenUpdating = True
  MsgBox "Starting columns: " & vbTab & TotCols & vbLf & _
          "Deleted columns: " & vbTab & DelCols & vbLf & _
          "Final columns: " & vbTab & TotCols - DelCols
End Sub
```


----------



## shinigamilight (Dec 24, 2022)

```
Sub comeout()
        Dim rng As Range
        Dim k As String
        Dim j As Integer
         k = Selection.Address
        On Error Resume Next
        Set rng = Application.InputBox("Enter Range", , k, , , , , 8)

        For j = rng.Columns.Count To 1 Step -1
                If WorksheetFunction.CountBlank(rng.Columns(j)) = (Range("xfd:xfd").Rows.Count) - 1 Then
                    rng.Columns(j).Delete
                End If
        Next j

End Sub
```


----------



## John Caines (Dec 25, 2022)

Hi Peter_SSs & shinigamilight,
Many thanks for both your replies, and BTW,,, Merry Christmas to you both (and anyone reading this).

Sorry for my late reply, it's been a hectic day!

1st off, Many thanks again Peter_SSs for your reply and new VBA code.
I've tried it out, and it works brilliantly! Many thanks for this. A real time saver for me.
I see what you mean now about cells appearing to the eye being blank, but in-fact they are not.
I tried your formula; 
=ISTEXT(D2)
And it returned TRUE,,, but when I clicked in cell D2 and hit delete, it then changed to FALSE,,, so yes, I see what you now mean Peter_SSs, the cells indeed are not blank.

Many thanks also shinigamilight for your code.
It worked fine, (I select the range, I just highlighted all the columns with headers in my sheet), and it ran perfectly.
So many thanks to you also.

This really will save me huge amounts of time, but if possible, is there any chance Peter_SSs or shinigamilight to have 1 final tweak to your code?
It's my mistake, as I can see there is 1 column that can also be deleted, (which appears often) but I wasn't aware of it / didn't think about this when I 1st posted.

TO DELETE ALSO:
I might have a column (Always has a header in ROW 1),, but the column might have blank cells (Well, as Peter_SSs has pointed out, they are not really blank) , and they might have the number 0 in.
If a column has this, blank cell and 0's in only,  it can also be deleted.
Why?
Well, this sheet in a lot of places does a count of the entries in the column to the right of it. So if the whole column has a blank cell or number 0, that column is really nothing, so can be deleted.
If it has blank cells, numbers 0,1,2,3,4,5 etc,, it wouldn't be deleted as there are numbers above 0 in the column.
I hope this makes sense.

If your code could be tweaked Peter_SSs or shinigamilight to also delete these columns (so also search for columns with only blanks and 0's in) it then would be perfect.
The software can actually export over 450 columns of data, so manually finding these and deleting them 1 by 1 is a real pain,, I mean really really! 
I include 1 example sheet with 2 columns of data, that just makes clear (I hope) the above 








						mr-excel-example-sheet-3-Peter_SSs-shinigamilight.xlsx
					

Shared with Dropbox




					www.dropbox.com
				




I really hope the above makes sense.
Again, thank you both for taking the time to look and help.

I appreciate this very much.

Hope you all have a wonderful Christmas.

Best regards
A very grateful
John C


----------



## Peter_SSs (Dec 25, 2022)

See how this goes


```
Sub Del_Cols_v3()
  Dim c As Long, rw As Long, TotCols As Long, DelCols As Long
  
  Application.ScreenUpdating = False
  TotCols = Cells(1, Columns.Count).End(xlToLeft).Column
  For c = TotCols To 1 Step -1
    rw = Columns(c).Find(What:="?*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    If rw - WorksheetFunction.CountIf(Columns(c).Resize(rw), 0) - WorksheetFunction.CountBlank(Columns(c).Resize(rw)) = 1 Then
      DelCols = DelCols + 1
      Columns(c).Delete
    End If
  Next c
  Application.ScreenUpdating = True
  MsgBox "Starting columns: " & vbTab & TotCols & vbLf & _
          "Deleted columns: " & vbTab & DelCols & vbLf & _
          "Final columns: " & vbTab & TotCols - DelCols
End Sub
```


----------



## John Caines (Dec 26, 2022)

Hello Peter_SSs,
Many thanks again for tweaking your VBA code again.
I've just tested it,,, and it ran perfectly!  

Brilliant Peter-SSs,, this is really really brilliant!
I just tested it on a sheet I ran the other day, which had 180 columns of data in. Manually this would have taken me ages, probably an hour I guess.
It just found and deleted 62 columns of data in,, well, maybe 2 seconds, not sure exactly how long it took, but it was quick.

So grateful for this Peter_SSs. This is a wonderful Christmas present! 

Have a great day, and hope the New Year brings you and everyone here a lot of joy and happiness.

Best regards
A very very grateful
John C


----------



## John Caines (Dec 26, 2022)

Hi Peter_SSs
Just to add,
I did try to comment it, as I read you can add a comment by adding a single quote mark at the start of the comment line.
And,,I broke it! 
I couldn't even get this right,,,, VBA just doesn't seem to like me much!

Heyho
Best regards
John C


----------



## Peter_SSs (Dec 26, 2022)

You're comment in the first image is in the correct place and is not the problem. The problem starts where vba has placed the blue block. Somehow you have duplicated a whole section of code. Get rid of the No. 2 section.


----------



## John Caines (Dec 26, 2022)

Hi Peter_SSs,
Unbelievable! I don't even know how I done that, and didn't realise!
LOL
I need another coffee I think, or something stronger seeing as it's Christmas!  

I just wanted to put a credit in, as this is so handy, If anyone else needs it on the software forum (software that this is being used on) I just wanted to credit appropriately.

Very glad you saw the error of my ways.

Cheers Peter_SSs

All the best and have a great day

Best regards
John C


----------



## Peter_SSs (Dec 26, 2022)

Cheers.


----------

