delete column if blank
 

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

delete column if blank
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"J.W. Aldridge" wrote in message
...
Data from A:AB.
Row 1 has headers.
If there is no data in any of the rows beneath headers, delete entire
column.

delete column if blank
 

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]


On Fri, 12 Feb 2010 10:49:26 -0800 (PST), "J.W. Aldridge"
wrote:

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

delete column if blank
 

Didn't quite work. Nothing visibly happened.

delete column if blank
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"J.W. Aldridge" wrote in message
...
Didn't quite work. Nothing visibly happened.

delete column if blank
 

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.

delete column if blank
 

Worked for me in 2003 and 2007


Gord Dibben MS Excel MVP

On Fri, 12 Feb 2010 11:06:21 -0800 (PST), "J.W. Aldridge"
wrote:

Didn't quite work. Nothing visibly happened.

delete column if blank
 

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" wrote:

Didn't quite work. Nothing visibly happened.

--

Dave Peterson

delete column if blank
 

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

again, thanx all!

delete column if blank
 

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.

delete column if blank
 

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.

"J.W. Aldridge" wrote:

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

delete column if blank
 

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]



On Fri, 12 Feb 2010 11:16:35 -0800 (PST), "J.W. Aldridge"
wrote:

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.

delete column if blank
 

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