marco to delete empty columns

[robyn_willson]

Hello. I need to make a macro that will delete any column in my excel
worksheet that is empty. I am not sure wich columns are empty so it will need
to check each one. I would also like it to ignore the first row because every
column has a header so the macro would not work if it checked the first row.
Does anyone have any ideas for a macro? I tryed to make one to do it today
but just ended up very frustrated.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub sonic()
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

For x = 1 To lastcolumn
If Cells(Rows.Count, x).End(xlUp).Row = 1 Then
Columns(x).EntireColumn.Delete
End If
Next

End Sub


Mike

 

[Mike H]

OOPS,

My mistake you have to do it backwards

Sub sonic()
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

For x = lastcolumn To 1 Step -1
rr = Cells(Rows.Count, x).End(xlUp).Row
If Cells(Rows.Count, x).End(xlUp).Row = 1 Then
Columns(x).EntireColumn.Delete
End If
Next

End Sub

Mike

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub sonic()
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

For x = 1 To lastcolumn
If Cells(Rows.Count, x).End(xlUp).Row = 1 Then
Columns(x).EntireColumn.Delete
End If
Next

End Sub


Mike

Hello. I need to make a macro that will delete any column in my excel
worksheet that is empty. I am not sure wich columns are empty so it will need
to check each one. I would also like it to ignore the first row because every
column has a header so the macro would not work if it checked the first row.
Does anyone have any ideas for a macro? I tryed to make one to do it today
but just ended up very frustrated.

 

[Rick Rothstein]

Since you didn't use the rr variable anywhere, you can remove this
assignment line from the code...

rr = Cells(Rows.Count, x).End(xlUp).Row

--
Rick (MVP - Excel)

OOPS,

My mistake you have to do it backwards

Sub sonic()
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

For x = lastcolumn To 1 Step -1
rr = Cells(Rows.Count, x).End(xlUp).Row
If Cells(Rows.Count, x).End(xlUp).Row = 1 Then
Columns(x).EntireColumn.Delete
End If
Next

End Sub

Mike

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub sonic()
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

For x = 1 To lastcolumn
If Cells(Rows.Count, x).End(xlUp).Row = 1 Then
Columns(x).EntireColumn.Delete
End If
Next

End Sub


Mike

Hello. I need to make a macro that will delete any column in my excel
worksheet that is empty. I am not sure wich columns are empty so it
will need
to check each one. I would also like it to ignore the first row because
every
column has a header so the macro would not work if it checked the first
row.
Does anyone have any ideas for a macro? I tryed to make one to do it
today
but just ended up very frustrated.