delete non adjacent columns when blank
 

I am looking for vb code to :

check B4:B300 for blank and if so delete entire column (B:B). I can get
that part. What I am having problems with is I have say 20 columns that I
need to check so I need to "select" the all correct columns and then delete.
I would like the code to "select" the column and/or columns that have blank
cells and then delete

thanks

delete non adjacent columns when blank
 

You can give this a try... it should be close to what you want...

Sub SelectBlanks()
Dim rng As Range
Dim rngAll As Range
Dim l As Long

Set rng = Range("B4:B300")
For l = 1 To 20
If Application.WorksheetFunction.CountBlank(rng) = rng.Cells.Count
Then
If rngAll Is Nothing Then
Set rngAll = rng
Else
Set rngAll = Union(rng, rngAll)
End If
End If
Set rng = rng.Offset(0, 1)
Next l
If Not rngAll Is Nothing Then
rngAll.EntireColumn.Select
If MsgBox("Delete these??", vbYesNo, "Delete") = vbYes Then _
rngAll.EntireColumn.Delete
End If
End Sub

--
HTH...

Jim Thomlinson


"Mona" wrote:

I am looking for vb code to :

check B4:B300 for blank and if so delete entire column (B:B). I can get
that part. What I am having problems with is I have say 20 columns that I
need to check so I need to "select" the all correct columns and then delete.
I would like the code to "select" the column and/or columns that have blank
cells and then delete

thanks

delete non adjacent columns when blank
 

thank you Jim. I tried the code but it is deleting everthing in columns 1
through 20. for example: I have data in column h and column j that I need
to keep. (this can change as I could have data in columns e, g, i)

example data
a b c d e f g h i j
date 3 4
date 1 2

in the end I would like:

a b c
date 3 4
date 1 2


thanks !

"Jim Thomlinson" wrote:

You can give this a try... it should be close to what you want...

Sub SelectBlanks()
Dim rng As Range
Dim rngAll As Range
Dim l As Long

Set rng = Range("B4:B300")
For l = 1 To 20
If Application.WorksheetFunction.CountBlank(rng) = rng.Cells.Count
Then
If rngAll Is Nothing Then
Set rngAll = rng
Else
Set rngAll = Union(rng, rngAll)
End If
End If
Set rng = rng.Offset(0, 1)
Next l
If Not rngAll Is Nothing Then
rngAll.EntireColumn.Select
If MsgBox("Delete these??", vbYesNo, "Delete") = vbYes Then _
rngAll.EntireColumn.Delete
End If
End Sub

--
HTH...

Jim Thomlinson


"Mona" wrote:

I am looking for vb code to :

check B4:B300 for blank and if so delete entire column (B:B). I can get
that part. What I am having problems with is I have say 20 columns that I
need to check so I need to "select" the all correct columns and then delete.
I would like the code to "select" the column and/or columns that have blank
cells and then delete

thanks

delete non adjacent columns when blank
 

Mona, instead of using union on all the columns just delete them.
For i = 21 to 2 Step - 1
If
Application.WorksheetFunction.CountBlank(Range(Cel ls(4,i),Cells(3000,i))
= _
Range(Cells(4,i),Cells(3000,i)).Cells.Count Then
Columns(i).Delete
Next

HTH

Charles

Mona wrote:
thank you Jim. I tried the code but it is deleting everthing in columns 1
through 20. for example: I have data in column h and column j that I need
to keep. (this can change as I could have data in columns e, g, i)

example data
a b c d e f g h i j
date 3 4
date 1 2

in the end I would like:

a b c
date 3 4
date 1 2


thanks !

"Jim Thomlinson" wrote:

You can give this a try... it should be close to what you want...

Sub SelectBlanks()
Dim rng As Range
Dim rngAll As Range
Dim l As Long

Set rng = Range("B4:B300")
For l = 1 To 20
If Application.WorksheetFunction.CountBlank(rng) = rng.Cells.Count
Then
If rngAll Is Nothing Then
Set rngAll = rng
Else
Set rngAll = Union(rng, rngAll)
End If
End If
Set rng = rng.Offset(0, 1)
Next l
If Not rngAll Is Nothing Then
rngAll.EntireColumn.Select
If MsgBox("Delete these??", vbYesNo, "Delete") = vbYes Then _
rngAll.EntireColumn.Delete
End If
End Sub

--
HTH...

Jim Thomlinson


"Mona" wrote:

I am looking for vb code to :

check B4:B300 for blank and if so delete entire column (B:B). I can get
that part. What I am having problems with is I have say 20 columns that I
need to check so I need to "select" the all correct columns and then delete.
I would like the code to "select" the column and/or columns that have blank
cells and then delete

thanks

delete non adjacent columns when blank
 

charles -
Perfect !! Many thanks, again

-mona

"Die_Another_Day" wrote:

Mona, instead of using union on all the columns just delete them.
For i = 21 to 2 Step - 1
If
Application.WorksheetFunction.CountBlank(Range(Cel ls(4,i),Cells(3000,i))
= _
Range(Cells(4,i),Cells(3000,i)).Cells.Count Then
Columns(i).Delete
Next

HTH

Charles

Mona wrote:
thank you Jim. I tried the code but it is deleting everthing in columns 1
through 20. for example: I have data in column h and column j that I need
to keep. (this can change as I could have data in columns e, g, i)

example data
a b c d e f g h i j
date 3 4
date 1 2

in the end I would like:

a b c
date 3 4
date 1 2


thanks !

"Jim Thomlinson" wrote:

You can give this a try... it should be close to what you want...

Sub SelectBlanks()
Dim rng As Range
Dim rngAll As Range
Dim l As Long

Set rng = Range("B4:B300")
For l = 1 To 20
If Application.WorksheetFunction.CountBlank(rng) = rng.Cells.Count
Then
If rngAll Is Nothing Then
Set rngAll = rng
Else
Set rngAll = Union(rng, rngAll)
End If
End If
Set rng = rng.Offset(0, 1)
Next l
If Not rngAll Is Nothing Then
rngAll.EntireColumn.Select
If MsgBox("Delete these??", vbYesNo, "Delete") = vbYes Then _
rngAll.EntireColumn.Delete
End If
End Sub

--
HTH...

Jim Thomlinson


"Mona" wrote:

I am looking for vb code to :

check B4:B300 for blank and if so delete entire column (B:B). I can get
that part. What I am having problems with is I have say 20 columns that I
need to check so I need to "select" the all correct columns and then delete.
I would like the code to "select" the column and/or columns that have blank
cells and then delete

thanks