Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Delete blanks between a range and populate only the names inthe given range | Excel Discussion (Misc queries) | |||
Delete columns of a cell range | Excel Discussion (Misc queries) | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
How to Delete a Range in Closed Workbook (to Replace Delete Query) | Excel Discussion (Misc queries) | |||
Delete columns | Excel Programming |