About Us

Mona

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Delete blank columns	Johannes_R	Excel Worksheet Functions	2	April 7th 09 07:33 PM
Automatically delete rows with any blank columns	Kris	Excel Worksheet Functions	6	December 11th 08 02:12 PM
Delete blank columns.	John	Excel Discussion (Misc queries)	7	November 29th 07 10:48 PM
Delete rows with certain columns blank	Slohcin	Excel Discussion (Misc queries)	2	November 1st 06 02:35 PM
Delete rows that contain blank columns	Lindsey	Excel Worksheet Functions	1	December 8th 05 10:34 PM

Menu

About Us