I have a list of items in a column. (say 20) I would like to be able to
enter an "x" in the cell to the left of certain items (say 4 of them) and
then run a macro that will delete the items. Does anyone have any ideas? I
need to be able to indicate with an €œx€� which items are no longer valid and
then run the macro at the end of a shift and clear just those items marked
with an €œx€�. Thanks!

assume column 3 (C) is where the x's appear

set rng = Columns(3).Specialcells(xlconstants,xltextvalues)
for each cell in rng
if lcase(cell.value) = "x" then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
Next
if not rng1 is nothing then
rng1.EntireRow.Delete ' to delete the row Or
'rng1.offset(0,1).ClearContents ' to just clear the cell to the
right

Of course if the only thing in this column will be the x's then you don't
need to do the looping

Columns(3).Specialcells(xlconstants,xltextvalues). EntireRow.Delete

as an example.

--
Regards,
Tom Ogilvy


"Husker87" wrote in message
...
I have a list of items in a column. (say 20) I would like to be able
to
enter an "x" in the cell to the left of certain items (say 4 of them) and
then run a macro that will delete the items. Does anyone have any ideas?
I
need to be able to indicate with an "x" which items are no longer valid
and
then run the macro at the end of a shift and clear just those items marked
with an "x". Thanks!

Tom...thanks for the help...

It actually would be nice if the macro cleared the entire row including the
€œx€�. I added the following macro but messed it up some how. It clears one
"x" at a time???

Sub test()
'
Set rng = Columns(3).SpecialCells(xlConstants, xlTextValues)
For Each cell In rng
If LCase(cell.Value) = "x" Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.Offset(0, 1).ClearContents
'
End Sub

Not being the best at this I'm missing something but can't quite figure it
out. Thanks agian for your help!!!


"Tom Ogilvy" wrote:

assume column 3 (C) is where the x's appear

set rng = Columns(3).Specialcells(xlconstants,xltextvalues)
for each cell in rng
if lcase(cell.value) = "x" then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
Next
if not rng1 is nothing then
rng1.EntireRow.Delete ' to delete the row Or
'rng1.offset(0,1).ClearContents ' to just clear the cell to the
right

Of course if the only thing in this column will be the x's then you don't
need to do the looping

Columns(3).Specialcells(xlconstants,xltextvalues). EntireRow.Delete

as an example.

--
Regards,
Tom Ogilvy


"Husker87" wrote in message
...
I have a list of items in a column. (say 20) I would like to be able
to
enter an "x" in the cell to the left of certain items (say 4 of them) and
then run a macro that will delete the items. Does anyone have any ideas?
I
need to be able to indicate with an "x" which items are no longer valid
and
then run the macro at the end of a shift and clear just those items marked
with an "x". Thanks!

This worked fine for me:

Sub test()
Dim rng As Range, rng1 As Range
Dim cell As Range
On Error Resume Next
Set rng = Columns(3).SpecialCells(xlConstants, xlTextValues)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "No x's"
Exit Sub
End If
For Each cell In rng
If LCase(cell.Value) = "x" Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.Offset(0, 1).EntireRow.ClearContents
End If

End Sub

I would expect it to work in xl2000 and later. If you are in xl97 it would
require some more code.

--
Regards,
Tom Ogilvy

"Husker87" wrote in message
...
Tom...thanks for the help...

It actually would be nice if the macro cleared the entire row including
the
"x". I added the following macro but messed it up some how. It clears
one
"x" at a time???

Sub test()
'
Set rng = Columns(3).SpecialCells(xlConstants, xlTextValues)
For Each cell In rng
If LCase(cell.Value) = "x" Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.Offset(0, 1).ClearContents
'
End Sub

Not being the best at this I'm missing something but can't quite figure it
out. Thanks agian for your help!!!


"Tom Ogilvy" wrote:

assume column 3 (C) is where the x's appear

set rng = Columns(3).Specialcells(xlconstants,xltextvalues)
for each cell in rng
if lcase(cell.value) = "x" then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
Next
if not rng1 is nothing then
rng1.EntireRow.Delete ' to delete the row Or
'rng1.offset(0,1).ClearContents ' to just clear the cell to the
right

Of course if the only thing in this column will be the x's then you
don't
need to do the looping

Columns(3).Specialcells(xlconstants,xltextvalues). EntireRow.Delete

as an example.

--
Regards,
Tom Ogilvy


"Husker87" wrote in message
...
I have a list of items in a column. (say 20) I would like to be
able
to
enter an "x" in the cell to the left of certain items (say 4 of them)
and
then run a macro that will delete the items. Does anyone have any
ideas?
I
need to be able to indicate with an "x" which items are no longer
valid
and
then run the macro at the end of a shift and clear just those items
marked
with an "x". Thanks!

Tom,

This worked GREAT! I can't thank you enough. (btw, as you might have
guessed it was operator error before)

"Tom Ogilvy" wrote:

This worked fine for me:

Sub test()
Dim rng As Range, rng1 As Range
Dim cell As Range
On Error Resume Next
Set rng = Columns(3).SpecialCells(xlConstants, xlTextValues)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "No x's"
Exit Sub
End If
For Each cell In rng
If LCase(cell.Value) = "x" Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.Offset(0, 1).EntireRow.ClearContents
End If

End Sub

I would expect it to work in xl2000 and later. If you are in xl97 it would
require some more code.

--
Regards,
Tom Ogilvy

"Husker87" wrote in message
...
Tom...thanks for the help...

It actually would be nice if the macro cleared the entire row including
the
"x". I added the following macro but messed it up some how. It clears
one
"x" at a time???

Sub test()
'
Set rng = Columns(3).SpecialCells(xlConstants, xlTextValues)
For Each cell In rng
If LCase(cell.Value) = "x" Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.Offset(0, 1).ClearContents
'
End Sub

Not being the best at this I'm missing something but can't quite figure it
out. Thanks agian for your help!!!


"Tom Ogilvy" wrote:

assume column 3 (C) is where the x's appear

set rng = Columns(3).Specialcells(xlconstants,xltextvalues)
for each cell in rng
if lcase(cell.value) = "x" then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
Next
if not rng1 is nothing then
rng1.EntireRow.Delete ' to delete the row Or
'rng1.offset(0,1).ClearContents ' to just clear the cell to the
right

Of course if the only thing in this column will be the x's then you
don't
need to do the looping

Columns(3).Specialcells(xlconstants,xltextvalues). EntireRow.Delete

as an example.

--
Regards,
Tom Ogilvy


"Husker87" wrote in message
...
I have a list of items in a column. (say 20) I would like to be
able
to
enter an "x" in the cell to the left of certain items (say 4 of them)
and
then run a macro that will delete the items. Does anyone have any
ideas?
I
need to be able to indicate with an "x" which items are no longer
valid
and
then run the macro at the end of a shift and clear just those items
marked
with an "x". Thanks!