Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Need Help Editing Cells
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. |
#2
Posted to microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Need Help Editing Cells
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 -- Gary "mike" wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Need Help Editing Cells
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mike" wrote: 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. |
#4
Posted to microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Need Help Editing Cells
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 mike wrote: 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. |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Need Help Editing Cells
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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Need Help Editing Cells
"jseven" wrote:
.. 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Need Help Editing Cells
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. -- Regards Roger Govier "Max" wrote in message ... "jseven" wrote: .. 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Need Help Editing Cells
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Roger Govier" wrote in message ... 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. -- Regards Roger Govier |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
editing liked cells | Excel Discussion (Misc queries) | |||
Need Help Editing Cells | New Users to Excel | |||
editing cells without using mouse | Excel Discussion (Misc queries) | |||
disable cells editing | Excel Discussion (Misc queries) | |||
editing cells... | Excel Programming |