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

[cpudenusa]

I started a new Excel document with arbitrary column
heading with some actually having data in that column.
I went to tools>macro> 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

 

[Tom Ogilvy]

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

 

[cpudenusa]

I will try this thank you for the reply

 

[Lance]

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