Need Help Editing Cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mike - 19 Sep 2006 02:57 GMT

I am using W XP and Excel 2003. I have an XLS file that was created
from a CSV file. Column D of the Xls file containd a great deal of data
that is still comma seperated. For example a row may have Ford, Chevy,
Dodge... Another row in the same column may have Dodge, Honda,
Toyota...

What I want to do is delete the contents of any cell in Column D that
does not contain Honda. Any cell that does contain Honda should have
any other data deleted except for Honda.

Thanks for any and all help.

Reply to this Message

Gary Keramidas - 19 Sep 2006 03:18 GMT

will this work for you?

Sub test2()
Dim cell As Range
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "d").End(xlUp).Row

For Each cell In ws.Range("d1:d" & lastrow)
If InStr(1, cell.Value, "Honda") > 0 Then
cell.Value = "Honda"
Else
cell.Value = ""
End If
Next

End Sub

Signature

Gary

>I am using W XP and Excel 2003. I have an XLS file that was created
> from a CSV file. Column D of the Xls file containd a great deal of data
[quoted text clipped - 7 lines]
>
> Thanks for any and all help.

Reply to this Message

Max - 19 Sep 2006 03:33 GMT

One way is to use a helper col, say col X ?
Try on a spare copy ..
Assuming data in col D is running in D2 down
Put in X2:
=IF(TRIM(D2)="","",IF(COUNTIF(D2,"*Honda*"),"Honda",D2))
Copy down to the last row of data in col D. Then copy col X and overwrite
col D with a "Paste special" as values. Delete col X to clean up.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I am using W XP and Excel 2003. I have an XLS file that was created
> from a CSV file. Column D of the Xls file containd a great deal of data
[quoted text clipped - 7 lines]
>
> Thanks for any and all help.

Reply to this Message

Max - 19 Sep 2006 07:34 GMT

Oops, upon a closer 2nd reading of your lines ..

> > .. What I want to do is delete the contents of any cell in Column D that
> > does not contain Honda...

Instead of:

> Put in X2:
> =IF(TRIM(D2)="","",IF(COUNTIF(D2,"*Honda*"),"Honda",D2))

It should just be in X2, copied down:
=IF(TRIM(D2)="","",IF(COUNTIF(D2,"*Honda*"),"Honda",""))

(The former simply returns the contents of col D where "Honda" is not found)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

jseven - 19 Sep 2006 03:33 GMT

I'd just slap a filter on it... data\autofilter

Then select "contains" then enter honda.
Then just copy Honda into all the filtered rows

Then undo that filter, and do it again
This time select "does not contain" then enter Honda

Then just delete all the contents of those cells

Remove the filter, and you're done.

Jamie

> I am using W XP and Excel 2003. I have an XLS file that was created
> from a CSV file. Column D of the Xls file containd a great deal of data
[quoted text clipped - 7 lines]
>
> Thanks for any and all help.

Reply to this Message

Max - 19 Sep 2006 07:39 GMT

> .. Then select "contains" then enter honda.
> Then just copy Honda into all the filtered rows

Problem is, think we gotta do that copying cell-by-cell in the filter mode's
visible cells for col D. You can't just copy n paste all at one go.

> Then undo that filter, and do it again
> This time select "does not contain" then enter Honda
> Then just delete all the contents of those cells

Ditto - the same problem as the above, we can't just "delete" (ie clear the
contents of the visible filtered rows) all at one go. Gotta clear it
cell-by-cell.

I've posted perhaps an easier way to do the above using a helper col.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Roger Govier - 19 Sep 2006 08:30 GMT

Hi Max

>You can't just copy n paste all at one go.
>we can't just "delete" (ie clear the contents of the visible filtered
>rows) all at one go

If you have Autofilter applied in the way described, you can.

Case 1
With filter applied, type Honda in the first visible cell, and use the
fill handle to copy down. Only the cells in the visible range will have
their contents altered to say Honda.
Case 2
With Autofilter in place, mark the range of cells in column D and press
Delete. Only the visible cells will have their contents deleted.

Signature

Regards

Roger Govier

>> .. Then select "contains" then enter honda.
>> Then just copy Honda into all the filtered rows
[quoted text clipped - 13 lines]
>
> I've posted perhaps an easier way to do the above using a helper col.

Reply to this Message

Max - 19 Sep 2006 08:56 GMT

Roger:
Thanks for correction! I must have erred terribly in testing it earlier.
Time for an oxy-break ..

jseven:
My sincere apologies. Your suggestion was good.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi Max
>
[quoted text clipped - 11 lines]
> With Autofilter in place, mark the range of cells in column D and press
> Delete. Only the visible cells will have their contents deleted.

Reply to this Message