Clear row based on column value

SDave

Board Regular
Joined
Aug 12, 2008
Messages
152
Office Version
  1. 365
Platform
  1. MacOS
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this with a copy of your worksheet

Code:
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

Well-known Member
Joined
May 4, 2009
Messages
1,412
Try this in the worksheet code module
Option Explicit

Code:
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

Board Regular
Joined
Apr 27, 2009
Messages
107
Office Version
  1. 365
Platform
  1. Windows
Bill,

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

SDave

Board Regular
Joined
Aug 12, 2008
Messages
152
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Board Regular
Joined
Aug 12, 2008
Messages
152
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
May 4, 2009
Messages
1,412
Try this in a copy of your worksheet

Code:
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

Board Regular
Joined
Aug 12, 2008
Messages
152
Office Version
  1. 365
Platform
  1. MacOS
Cheers Bill, I can't thank you enough, the code you supplied worked a treat!!! Much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top