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

  • Thread starter Thread starter cpudenusa
  • Start date Start date
C

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
 
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
 
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
 
Back
Top