Delete Columns For A Range
 

I'm trying to delete columns (not rows) within Row 1, that contain a blank
cell. I have examples of looping through the sheet, but not a specific row.
The below deletes rows within a range. Can someone modify it for columns?


Sub DeleteEmptyColumnRange()
Dim DelRange As Range
Dim c As Range
For Each c In ActiveSheet.Range("A1:AK1").Cells
If c.Value = 0 Then
If DelRange Is Nothing Then
Set DelRange = c.EntireRow
Else
Set DelRange = Union(DelRange, c.EntireRow)
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

Delete Columns For A Range
 

Sub DeleteEmptyColumnRange()
Dim DelRange As Range
Dim c As Range
For Each c In ActiveSheet.Range("A1:AK1").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

or

Sub DeleteColumnForBlankCellRow1()
dim rng as Range
On Error Resume Next
set rng = ActiveSheet.Range("A1:AK1").SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
rng.Entirecolumn.Delete
End if
End sub

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
I'm trying to delete columns (not rows) within Row 1, that contain a blank
cell. I have examples of looping through the sheet, but not a specific
row.
The below deletes rows within a range. Can someone modify it for columns?


Sub DeleteEmptyColumnRange()
Dim DelRange As Range
Dim c As Range
For Each c In ActiveSheet.Range("A1:AK1").Cells
If c.Value = 0 Then
If DelRange Is Nothing Then
Set DelRange = c.EntireRow
Else
Set DelRange = Union(DelRange, c.EntireRow)
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

Delete Columns For A Range
 

Hi Scott,
This worked for me:

Sub DelColumn()
r = 1 ' r is row number
For i = 37 To 1 Step -1 ' column numbers, work from right to left
If Cells(r, i) = "" Then Columns(i).Delete Shift:=xlToLeft
Next i

End Sub

HTH

Andrew Bourke



scott wrote:
I'm trying to delete columns (not rows) within Row 1, that contain a blank
cell. I have examples of looping through the sheet, but not a specific row.
The below deletes rows within a range. Can someone modify it for columns?


Sub DeleteEmptyColumnRange()
Dim DelRange As Range
Dim c As Range
For Each c In ActiveSheet.Range("A1:AK1").Cells
If c.Value = 0 Then
If DelRange Is Nothing Then
Set DelRange = c.EntireRow
Else
Set DelRange = Union(DelRange, c.EntireRow)
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