delete columns with 0 in single row

[paul mueller]

Hi all, the code below essentially works but i don't want it to sort. If I
remove cells.select down to Application.goto...it no longer works properly.


Cells.Select
Selection.Sort Key1:=Range("A2"), _
Orientation:=xlLeftToRight
Application.Goto Reference:="R2C1"
While IsEmpty(ActiveCell) = False
If ActiveCell = 0 Then
ActiveCell.EntireColumn.Delete
ElseIf ActiveCell > 0 Then
ActiveCell.Offset(0, 1).Range("A1").Select
End If
Wend

 

[Jim Rech]

It's good if you tell us what you want to do. It looks like you want to
delete any column on the active sheet where the value in row 2 is "0". If
that's the case this routines does it for me:

Sub DelColsWith0()
Dim Cell As Range
Dim DelCols As Range
For Each Cell In Rows(2).Cells
If Cell.Value = "0" Then
If DelCols Is Nothing Then
Set DelCols = Cell
Else
Set DelCols = Union(DelCols, Cell)
End If
End If
Next
If Not DelCols Is Nothing Then DelCols.EntireColumn.Delete
End Sub


--
Jim Rech
Excel MVP
| Hi all, the code below essentially works but i don't want it to sort. If
I
| remove cells.select down to Application.goto...it no longer works
properly.
|
|
| Cells.Select
| Selection.Sort Key1:=Range("A2"), _
| Orientation:=xlLeftToRight
| Application.Goto Reference:="R2C1"
| While IsEmpty(ActiveCell) = False
| If ActiveCell = 0 Then
| ActiveCell.EntireColumn.Delete
| ElseIf ActiveCell > 0 Then
| ActiveCell.Offset(0, 1).Range("A1").Select
| End If
| Wend
|
|

 

[Paul Mueller]

yes you are right, I will be more descriptive the next time.

Thank you for the code example, it worked perfectly.