delete columns vba - but ignore 1st row

[John Caines]

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]

Change the zero to 1

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

 

[John Caines]

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.xlsx
	A	B	C	D	E	F	G	H	I
1	Country – DNS : Host	Country ISO Code – DNS : Host	Continent – DNS : Host	Linking C Blocks – Moz : Root Domain	Links – Moz : Root Domain	External Links – Moz : Root Domain	External Equity-Passing Links – Moz : Root Domain	Root Domains Linking – Moz : Root Domain	Time Last Crawled – Moz : URL
2	United States	US	North America						10/12/2018 1:00
3	United States	US	North America						10/15/2018 1:00
4	United States	US	North America						10/10/2018 1:00
5	India	IN	Asia						10/12/2018 1:00
6	Germany	DE	Europe						11/7/2018 0:00
7	India	IN	Asia						10/11/2018 1:00
8									12/19/2018 0:00
9	France	FR	Europe						12/12/2018 0:00
10	United States	US	North America						10/1/2018 1:00
11	United States	US	North America						10/10/2018 1:00
12	Germany	DE	Europe						10/15/2018 1:00
13	Canada	CA	North America						10/3/2018 1:00
14	United States	US	North America						10/6/2018 1:00
15	United States	US	North America						8/4/2017 1:00
16	United States	US	North America						8/24/2018 1:00
17	Cyprus	CY	Europe						11/15/2018 0:00
18	Singapore	SG	Asia						10/5/2018 1:00
19	United States	US	North America						10/14/2018 1:00
20	United States	US	North America						10/13/2018 1:00
21	United States	US	North America						10/5/2018 1:00
22	Bulgaria	BG	Europe						11/2/2018 0:00
23	India	IN	Asia						10/22/2018 1:00
24	Canada	CA	North America						10/8/2018 1:00
25	United States	US	North America						10/17/2018 1:00
26	United Kingdom	GB	Europe						11/3/2018 0:00
27	United States	US	North America						10/9/2018 1:00
28									9/22/2018 1:00
29	United States	US	North America						10/2/2018 1:00
30	United States	US	North America						10/15/2018 1:00
31	France	FR	Europe						1/15/2017 0:00
32	Singapore	SG	Asia						8/10/2018 1:00
33	Bahrain	BH	Asia						11/24/2018 0:00
34	United States	US	North America						11/17/2018 0:00
35	India	IN	Asia						12/12/2018 0:00
36	United Kingdom	GB	Europe						7/2/2018 1:00
37	United States	US	North America						10/10/2018 1:00
38	Germany	DE	Europe						10/13/2018 1:00
39									10/17/2018 1:00
40	United States	US	North America						10/21/2018 1:00
41	United States	US	North America						12/17/2018 0:00
42	United States	US	North America						10/27/2018 1:00
43	United States	US	North America						11/12/2018 0:00
44	Netherlands	NL	Europe						11/7/2018 0:00
45	United States	US	North America						8/6/2018 1:00
46	United States	US	North America						10/9/2018 1:00
47	United States	US	North America						11/30/2017 0:00
48	United States	US	North America						10/4/2018 1:00
49									10/22/2018 1:00
50	United States	US	North America						12/3/2018 0:00
51	United States	US	North America						11/1/2018 0:00
52									9/27/2018 1:00
53	United States	US	North America						12/11/2018 0:00
54									5/28/2018 1:00
55	United States	US	North America						10/27/2018 1:00
56	United States	US	North America						6/19/2017 1:00
57	Singapore	SG	Asia						9/30/2018 1:00
Sheet1

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]

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]

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]

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]

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]

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]

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]

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