inserted code into project but can't run it in run dialogbox
 

I started a new Excel document with arbitrary column
heading with some actually having data in that column.
I went to toolsmacro VBA editor
created a new module pasted this code in:

(it deletes columns having no data in them)


Public Sub GetRidofEmptyColumns(DeleteRange As Range)
Dim cCount As Integer, c As Integer
If DeleteRange Is Nothing Then Exit Sub
If DeleteRange.Areas.Count 1 Then Exit Sub
With DeleteRange
cCount = .Columns.Count
For c = cCount To 1 Step -1
If Application.CountA(.Columns(c)) = 0 Then
.Columns(c).EntireColumn.Delete
End If
Next c
End With
End Sub

How to I get this to run?
thank you,
den

inserted code into project but can't run it in run dialogbox
 

Since you macro requires an argument, it isn't shown.

You might want to modify it to

Public Sub GetRidofEmptyColumns()
Dim cCount As Integer, c As Integer
Dim DeleteRange as Range
set DeleteRange = Selection.EntireColumn
If DeleteRange Is Nothing Then Exit Sub
If DeleteRange.Areas.Count 1 Then Exit Sub
With DeleteRange
cCount = .Columns.Count
For c = cCount To 1 Step -1
If Application.CountA(.Columns(c)) = 0 Then
.Columns(c).EntireColumn.Delete
End If
Next c
End With
End Sub

This will work on the selected columns and can be run from
Tools=Macro=Macros dialog.

Otherwise you could keep the original and call it from another macro

Public Sub CleanColumns()
GetRidofemptyColumns Selection.Entirecolumn
End Sub

--
Regards,
Tom Ogilvy


cpudenusa wrote in message
...
I started a new Excel document with arbitrary column
heading with some actually having data in that column.
I went to toolsmacro VBA editor
created a new module pasted this code in:

(it deletes columns having no data in them)


Public Sub GetRidofEmptyColumns(DeleteRange As Range)
Dim cCount As Integer, c As Integer
If DeleteRange Is Nothing Then Exit Sub
If DeleteRange.Areas.Count 1 Then Exit Sub
With DeleteRange
cCount = .Columns.Count
For c = cCount To 1 Step -1
If Application.CountA(.Columns(c)) = 0 Then
.Columns(c).EntireColumn.Delete
End If
Next c
End With
End Sub

How to I get this to run?
thank you,
den

inserted code into project but can't run it in run dialogbox
 

I will try this thank you for the reply



-----Original Message-----
I started a new Excel document with arbitrary column
heading with some actually having data in that column.
I went to toolsmacro VBA editor
created a new module pasted this code in:

(it deletes columns having no data in them)


Public Sub GetRidofEmptyColumns(DeleteRange As Range)
Dim cCount As Integer, c As Integer
If DeleteRange Is Nothing Then Exit Sub
If DeleteRange.Areas.Count 1 Then Exit Sub
With DeleteRange
cCount = .Columns.Count
For c = cCount To 1 Step -1
If Application.CountA(.Columns(c)) = 0 Then
.Columns(c).EntireColumn.Delete
End If
Next c
End With
End Sub

How to I get this to run?
thank you,
den
.

inserted code into project but can't run it in run dialogbox
 

You need to be able to pass the argument into
deletedrange. You could write a macro to do it, example

Sub testit()
Call GetRidofEmptyColumns(Selection)
End Sub


Lance
-----Original Message-----
I started a new Excel document with arbitrary column
heading with some actually having data in that column.
I went to toolsmacro VBA editor
created a new module pasted this code in:

(it deletes columns having no data in them)


Public Sub GetRidofEmptyColumns(DeleteRange As Range)
Dim cCount As Integer, c As Integer
If DeleteRange Is Nothing Then Exit Sub
If DeleteRange.Areas.Count 1 Then Exit Sub
With DeleteRange
cCount = .Columns.Count
For c = cCount To 1 Step -1
If Application.CountA(.Columns(c)) = 0 Then
.Columns(c).EntireColumn.Delete
End If
Next c
End With
End Sub

How to I get this to run?
thank you,
den
.