OK here goes. Sometimes it is necessary to go through an exported spreadsheet that has hundreds of columns. But let's say only 25% of the columns have data I am interested in. But every single column has a top row header with data in it. Is there a quick and efficient way to delete all columns that have data in the first row but nothing below that first row? Highlighting each column and manually deleting is just not feasible.

TIA

2 ...

Report Post

Posted by mdomingue

Lafayette, LA

Member since Nov 2010

37680 posts

Posted on 6/26/24 at 10:14 am to Daygo85

Is the second cell in the column you want to delete always empty? If so, you could pretty easily write a macro to do it. This should work for what you want to do if the second cell is always empty in the column you wish to delete.

Sub Macro1()
'
' Macro1 Macro
'

'
While Not (IsEmpty(ActiveCell.Value))
If IsEmpty(ActiveCell.Offset(1, 0).Range("A1").Value) Then
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Select
Else
ActiveCell.Offset(0, 1).Range("A1").Select
End If
Wend
End Sub

ETA fixed it to repeat until top of column is an empty cell

This post was edited on 6/26/24 at 10:21 am

0 ...

Report Post

Posted by Chromdome35

Fast lane, behind a slow driver

Member since Nov 2010

7661 posts

Posted on 6/26/24 at 11:10 am to Daygo85

Without having to use a macro...

Use a Helper Row:

Insert a new row just below the header row (so it becomes the new row 2).
In each cell of this new row, enter a formula to check if the column below the header is empty. For example, in cell A2, enter:

=IF(COUNTA(A3:A1048576)=0, "Delete", "")
Drag this formula across all columns.

Filter and Delete:
Select the entire row 2.
Apply a filter by going to the Data tab and selecting Filter.
Filter row 2 to show only cells containing "Delete".
Highlight all visible columns (those marked "Delete").
Right-click on one of the selected column headers and choose Delete Column.

Remove Filter and Helper Row:
Remove the filter by returning to the Data tab and clicking Filter again.
Delete the helper row (row 2).

1 ...

Report Post

Posted by mdomingue

Lafayette, LA

Member since Nov 2010

37680 posts

Posted on 6/26/24 at 11:46 am to Chromdome35

quote:
Without having to use a macro...

Use a Helper Row:

Insert a new row just below the header row (so it becomes the new row 2).
In each cell of this new row, enter a formula to check if the column below the header is empty. For example, in cell A2, enter:

=IF(COUNTA(A3:A1048576)=0, "Delete", "")
Drag this formula across all columns.

Filter and Delete:
Select the entire row 2.
Apply a filter by going to the Data tab and selecting Filter.
Filter row 2 to show only cells containing "Delete".
Highlight all visible columns (those marked "Delete").
Right-click on one of the selected column headers and choose Delete Column.

Remove Filter and Helper Row:
Remove the filter by returning to the Data tab and clicking Filter again.
Delete the helper row (row 2).