Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete column if blank
Didn't quite work. Nothing visibly happened.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete column if blank
.....data was originally downloaded/uploaded into excel. Not reading as
text. that's the issue. again, thanx all! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete row if blank in a column | Excel Worksheet Functions | |||
Delete Rows if any cell in Column H is blank but do not Delete Fir | Excel Programming | |||
How to delete row if column is blank or certain text | Excel Programming | |||
If field in column is blank, delete if.... | Excel Programming | |||
Delete Entire Row If Column C is Blank | Excel Programming |