delete column if blank

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

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
 
C

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]
 
R

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

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

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

J.W. Aldridge

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

again, thanx all!
 
J

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

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top