delete column if blank

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

 

[Ron de Bruin]

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

 

[Chip Pearson]

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]

 

[J.W. Aldridge]

Didn't quite work. Nothing visibly happened.

 

[Ron de Bruin]

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

 

[J.W. Aldridge]

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.

 

[Gord Dibben]

Worked for me in 2003 and 2007


Gord Dibben MS Excel MVP

 

[Dave Peterson]

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.

 

[J.W. Aldridge]

.....data was originally downloaded/uploaded into excel. Not reading as
text. that's the issue.

again, thanx all!

 

[J.W. Aldridge]

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.

 

[Dave Peterson]

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.

 

[Chip Pearson]

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]

 

[J.W. Aldridge]

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