Clear row based on column value

[SDave]

Is it possible to construct a VBA code to clear a row based on a column value?

The current worksheet I am using requires the end user to populate column C, which ranges from C14:C50 with one of the following values:

Current
Leaver
New Joiner

If either current or new joiner is selected, say in cell C14 then all the corresponding data in cells D14:U14 should remain as is. However if leaver is selected I would like corresponding data to be cleared. That said I should mention that columns P and M contain formulas which link elsewhere within my workbook.

My column range is C14:C50, and my row range is D14:U50.

If it helps I am more than happy to email a copy of my workbook across.

Any help would be greatly appreciated.

Thanks,

Sam

 

[VoG]

Try this with a copy of your worksheet

Sub test()
Dim i As Long
For i = 50 To 14 Step -1
    If LCase(Range("C" & i).Value) = "leaver" Then Rows(i).Delete
Next i
End Sub

 

[Bill Rockenbach]

Try this in the worksheet code module
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C14:C50")) Is Nothing Then Exit Sub
If Target.Value = "Leaver" Then Target.Offset(0, 1).Resize(1, 18).ClearContents
End Sub

 

[swayp11]

Bill,

I built similar code, and it clears the contents, but then gives me a Type Mismatch error. Do you know why?

 

[SDave]

Forgive me chaps but I am a complete and utter novice when it comes to VBA. I think I may need a blow by blow dummies guide....

I know I've made a complete hash of things because no matter which code I enter, no changes appear to be made at the front end?! HELP!!!

Two quick questions, would it be possible for me to skip over columns P and M so that my formulas remain intact, and secondly rather than clearning the entire row, would it be possible for the word Vacant to be entered in columns D to I, and then K, L, O, R, S, T, U?!

 

[SDave]

It would appear my laptop was having a slight spasm, both codes work - thanks guys.

Now is there a way to protect the formulas that I have in columns P and M so that they remain as is, instead of being cleared?

Secondly, and I know that this may be a slight stretch - rather than clearning the entire row, would it be possible for the word Vacant to be entered in columns D to I, and then K, L, O, R, S, T, U?!

Once again any help would be much appreciated.

Thanks,

Sam

 

[Bill Rockenbach]

Try this in a copy of your worksheet

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    'map
    '4 to 9           11 12 15 18 19 20 21  13 16
    'D to I, and then K, L, O, R, S, T, U?! M  P
    If Intersect(Target, Range("C14:C50")) Is Nothing Then Exit Sub
    If Target.Value = "Leaver" Then
        For i = 4 To 21
            Select Case i
            Case 4 To 9, 11, 12, 15, 18 To 21
                Cells(Target.Row, i) = "Vacant"
            Case 10, 14, 17
                Cells(Target.Row, i).ClearContents
            Case 13, 16
                'do nothing
            Case Else
                MsgBox "Column unaccounted for"
            End Select
        Next i
    End If
End Sub

 

[SDave]

Cheers Bill, I can't thank you enough, the code you supplied worked a treat!!! Much appreciated.