Delete Columns if Empty within a Range

Topic

New Reply

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.

Reply | Quote

Replies

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.

33641-Delcol1

33642-Delcol2

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

Reply | Quote

Worked a charm,
Thanks

Reply | Quote

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

Reply | Quote

XP,

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!?

Reply | Quote

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

Reply | Quote

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..

Reply | Quote

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

Reply | Quote

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!

Reply | Quote