Delete empty columns that have labels in row 1.

[jskasango]

I'm using Excel 2016 Professional and I need help with deleting completely blank columns from my active worksheet. Row 1 contains labels.

 

[Kaper]

I'd use a simple VBA macro for this. Press Alt+F11 to open Visual Basic Editor. In menu: Insert -> Module
in a window which opens paste the code:

Sub empty_cols_remover()
Dim lc As Long, i As Long, counter As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For i = lc To 1 Step -1
  counter = WorksheetFunction.CountA(Range(Cells(2, i), Cells(Rows.Count, i)))
  If counter = 0 Then Cells(1, i).EntireColumn.Delete shift:=xlToLeft
Next i
End Sub

Close VBE (this will take you back to your workbook), press Alt+F8 and run the macro (it willl be on a list)
the result shall be as presented below:

Before:

żarówki - solver.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Nagłowek1	Nagłowek2	Nagłowek3	Nagłowek4	Nagłowek5	Nagłowek6	Nagłowek7	Nagłowek8	Nagłowek9	Nagłowek10	Nagłowek11	Nagłowek12	Nagłowek13
2									1
3
4
5
6			aaa			aaa
7
8
9
10
11
12
13
14
15
Sheet2

Cell Formulas
Range	Formula
F6	F6	=C6

After:

żarówki - solver.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Nagłowek3	Nagłowek6	Nagłowek9
2			1
3
4
5
6	aaa	aaa
7
Sheet2

Cell Formulas
Range	Formula
B6	B6	=A6

BTW. If it is just "single or rare action", you may ignore warnings and save the file as not macro-enabled format. the code will not be there when you reopen the file. But if you plan to reuse the code, save the workbook in macro-enabled format like xlsm or xlsb. Then next time make worksheet with table active then Alt+F8 and run the macro.,

 

[DanteAmor]

Here is another macro for you to consider:

Sub Delete_Columns()
  Dim j As Long
  For j = Cells(1, Columns.Count).End(1).Column To 1 Step -1
    If Cells(Rows.Count, j).End(3).Row = 1 Then Columns(j).Delete
  Next
End Sub

 

[Kaper]

Minor (rather no practical meaning) problem with

Cells(Rows.Count, j).End(3).Row = 1

code is that it is true in case of both empty column, and really full column in case of current excel formats rows 1 to
1048576
But of course similar applies to all columns - if we have headers in A1:XFD1 both above codes will fail.