delete column if blank

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

Data from A:AB.
Row 1 has headers.
If there is no data in any of the rows beneath headers, delete entire
column.
 
Try this

Sub Test()
Dim I As Long
For I = 28 To 1 Step -1
If Application.WorksheetFunction.CountA(Columns(I)) = 1 Then
Columns(I).Delete
End If
Next I
End Sub
 
Try

Sub AAA()
Dim StartCol As Long
Dim EndCol As Long
Dim ColNdx As Long

StartCol = 1 ' column A
EndCol = 28 ' column AB

For ColNdx = EndCol To StartCol Step -1
If Application.CountA(Columns(ColNdx)) = 1 Then
Columns(ColNdx).Delete
End If
Next ColNdx
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
Then the cells below the header are not empty
If there is a space in a cell for example the counta function count that

Try in a new test workbook and you will see that it will work
 
ok... Didn't work on my sheet. But created a blank one with same
criteria and it worked. Thanx...
Will have to dig into why not working on original though.

Thanx again Sirs.
 
Do you have formulas that evaluate to ="" (or worse =" ")?

_DID_ you have formulas that evaluated to ="" and you converted to values?

Those cells aren't empty according to excel. And =counta() will see them, too.
But there are ways to fix it.
 
.....data was originally downloaded/uploaded into excel. Not reading as
text. that's the issue.

again, thanx all!
 
sheet was originally calculated prior to my handling. no formulas
evident, just know that it wont allow me to change format easily and
code didn't recognize them when i ran them both.
 
If you find a column that's not deleted (say column X), you can use this in a
cell (not in column X):

=counta(x:x)

What do you get back?

If it's more than 0, than there's something in that column. Your job will be to
find out what and where.
 
If it isn't deleting what you think it should, you might have a space
character in a cell, so while it looks empty, it isn't really empty.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
Thanx all

Evidently, the blank cells aren't blank. I went over the empty columns
and cleared contents. When i re-ran the code, it works. So, i just
gotta get rid of those and I'll be good to go.

thanx again
 
Back
Top