Passing a Cell Range

[scott]

Below code deletes columns with empty cells in a certain range. Ideally, I'd
rather just pass "B" for example the used range instead of having to pass a
start and end cell for the range.

I'm getting error

Usage: Call DeleteEmptyColumnRange(A1, AM1)

Sub DeleteEmptyColumnRange(ByVal sBeginCell As String, ByVal sEndCell As
String)

Dim DelRange As Range
Dim c As Range

For Each c In ActiveSheet.Range(sBeginCell & ":" & sEndCell).Cells
If IsEmpty(c.Value) Then
If DelRange Is Nothing Then
Set DelRange = c.EntireColumn
Else
Set DelRange = Union(DelRange, c.EntireColumn)
End If
End If
Next c
'turn on error handling in case no range is assigned

On Error Resume Next
DelRange.Delete
On Error GoTo 0

End Sub

 

[Jim Thomlinson]

Here is my implimentation of your code passuing a range... I also cleaned up
the Delete as you don't really need the error handling.

Sub test()
DeleteEmptyColumnRange (ActiveSheet.Range("A1:AM1"))
End Sub

Public Sub DeleteEmptyColumnRange(ByVal rngToDelete As Range)

Dim DelRange As Range
Dim c As Range

For Each c In rngToDelete
If IsEmpty(c.Value) Then
If DelRange Is Nothing Then
Set DelRange = c.EntireColumn
Else
Set DelRange = Union(DelRange, c.EntireColumn)
End If
End If
Next c

If Not DelRange Is Nothing Then DelRange.Delete

End Sub


I think this is what you want.

HTH