Select and Delete Columns

J

jacqui

I'd like to select and delete a range of columns on a
worksheet which are identified by each containing a value
of 0 in row 3, (they are sorted so they are grouped
together as well which makes it handy). The other columns
will each contain a value > 0 in row 3. I'm tying to suss
out the following
Would I use a Do Loop or a For Next?
Would I work left to right, or right to left using a step -
1 type statement
there is already a variable called lastcol that contains
the last used column
Once the columns are identified a need to delete the
entire column
Can anyone help with the syntax.
Many thanks
Jacqui
 
J

jacqui

Rob,
Thanks for your suggestion. Unfortunately the i counter
doesn't work when the code finds a column to delete 'cause
what should be the next column to test becomes the current
column in the loop and then the counter increments again
so it's effectively skipping a column each time. That's
why I was going to work the loop from right to left rather
than left to right.
Can you help I'm still stuck with the syntax
Jacqui
 
L

Lakeuk

The following code is an example that will work for you, may need
modifying if you have a large spreadsheet as my example only deletes
one column at a time.

Dave, Kendal


Sub test()
Cells.Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
Application.Goto Reference:="R3C1"
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
End Sub
 
Top