Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Cell Range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Cell Range
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 "scott" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a Range | Excel Programming | |||
Passing a range into a VBA function | Excel Programming | |||
passing range to c# | Excel Programming | |||
Passing cell addressess to variables to be use in range | Excel Programming | |||
Passing range to subprocedure - maybe? | Excel Programming |