Select and Delete Columns

  • Thread starter Thread starter jacqui
  • Start date Start date
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
 
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
 
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
 
Back
Top