delete columns with 0 in single row
 

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

delete columns with 0 in single row
 

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
"paul mueller" wrote in message
...
| 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
|
|

delete columns with 0 in single row
 

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

Thank you for the code example, it worked perfectly.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!