![]() |
![]() |
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
![]() |
|
|
Thread Tools | Display Modes |
|
|
#1 |
|
New Member
Join Date: May 2002
Posts: 9
|
Hi im trying to figure out if its possible to clear the contents of a cell by setting a certain value in another cell for example what I want to do is when the value of cell B is set to "in" i want that to clear cells c through h but only for that particular row if that makes sense. Thanks for any help
|
|
|
|
|
|
#2 |
|
MrExcel MVP
Join Date: Apr 2002
Location: Vancouver BC , Canada
Posts: 6,259
|
Will this do the trick for you ?
Put this code into the code window for the specific sheet and the deletion should occur. Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Trim(Range("B" & Target.Row).Value)) = "IN" And Target.Column = 2 Then
Range("$C$" & Target.Row & "1:$H$" & Target.Row).Clear
End If
End Sub
-- right click on sheet tab -- select "view code" (vb window will open) -- in left "project window" of VB editor double click on desired sheet icon -- paste the code for above into the Right blank Module area for the specific sheet. -- close VB editor and test |
|
|
|
|
|
#3 |
|
New Member
Join Date: May 2002
Posts: 9
|
Worked like a charm! Thank You Very Much!
|
|
|
|
|
|
#4 |
|
MrExcel MVP
Join Date: Apr 2002
Location: Vancouver BC , Canada
Posts: 6,259
|
Cheers...
__________________
<MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee> |
|
|
|
|
|
#5 |
|
New Member
Join Date: May 2002
Posts: 9
|
one problem though when it clears the cell it also clears out any conditional formatting rules or validations. Is there any way to clear just the text only and not any of the formatting or validations?
|
|
|
|
|
|
#6 |
|
MrExcel MVP
Join Date: Apr 2002
Location: Vancouver BC , Canada
Posts: 6,259
|
How about this ?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Trim(Range("B" & Target.Row).Value)) = "IN" And Target.Column = 2 Then
Range("$C$" & Target.Row & "1:$H$" & Target.Row).Value = ""
End If
End Sub
__________________
<MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee> |
|
|
|
|
|
#7 |
|
New Member
Join Date: May 2002
Posts: 9
|
Perfect!! Thank You!
|
|
|
|
![]() |
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|