Delete Row Data

Tony12zz

Board Regular
Joined
Feb 21, 2004
Messages
56
This bit of code (which I got from another thread) deletes any entry in column b or c if a is empty (or not a date).

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect([B:C], Target) Is Nothing Then Exit Sub
If Not IsDate(Range("A" & Target.Row)) Or IsEmpty(Range("A" & Target.Row)) Then Target.ClearContents
End Sub

How can It be modified to delete any entry if the previous cell is empty ie not allow anything in B if A is empty or C if B is empty etc

Regards

Tony
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Tony12zz

Board Regular
Joined
Feb 21, 2004
Messages
56
Any ideas anybody?

It does not have to be the same piece of code just something that stops a user entering something into a column if the previous column is empty?

:eek:
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi Tony, do you want to look at the left cell everywhere in the sheet or limit this to a particular column (ie just B & C)?

Im not sure if this is what you want. It only looks at the cell changed so if you delete the cell in Col A then Col B wont be blanked out for example.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect([B:C], Target) Is Nothing Then Exit Sub
If IsEmpty(Cells(Target.Row, Target.Column - 1)) Then Target.ClearContents
End Sub

hth
 

Tony12zz

Board Regular
Joined
Feb 21, 2004
Messages
56
Parry

I am trying to look at every left cell, so that if it is empty it does not allow data entry into the cell next to it ie A is empty nothing can be entered in B.

I think you code works although I deleted the:

If Intersect([B:C], Target) Is Nothing Then Exit Sub

Regards
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355

ADVERTISEMENT

Cool. You will need to check if the cell is in column A though or else it will fail because theres no column to the left. :biggrin:
 

Tony12zz

Board Regular
Joined
Feb 21, 2004
Messages
56
I have a formula in column A so the following works well b onwards,

Private Sub Worksheet_Change(ByVal Target As Range)

If IsEmpty(Cells(Target.Row, Target.Column - 1)) Then Target.ClearContents

End Sub

however I was trying to get a message box to display to let the user know what they should be doing however the following seems to get stuck into a loop ?

PROMPT = "Data must be entered consecutively ie column B must be completed before data is entered column C"
Title = "Data Error"
ANS = MsgBox(PROMPT, vbOKOnly, Title)
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355

ADVERTISEMENT

My fault, I wasnt thinking. When you make a change to a cell in code then this in turn causes the worksheet change event to run again so gets in a perpetual loop. Its something you have to watch out for with any change event. Change events dont run however if events are disabled so the trick is to turn them off, change your cell, then turn them back on again.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Dont do anything if multiple cells are changed
If Target.Cells.Count > 1 Then Exit Sub
'Dont do anything if cell is in column A
If Not Intersect([A:A], Target) Is Nothing Then Exit Sub

'Test if cell to the left is empty
If IsEmpty(Cells(Target.Row, Target.Column - 1)) Then
'Turn off events
Application.EnableEvents = False
'Clear contents if cell to the left is empty.
Target.ClearContents
'Turn events back on
Application.EnableEvents = True
'Produce message
PROMPT = "Data must be entered consecutively ie column B must be completed before data is entered column C"
Title = "Data Error"
MsgBox PROMPT, vbOKOnly, Title
End If

End Sub

hth

EDIT: Turning off events needs to be inside the IF statement. Change to code.
 

Tony12zz

Board Regular
Joined
Feb 21, 2004
Messages
56
Private Sub Worksheet_Change(ByVal Target As Range)
'Dont do anything if multiple cells are changed
If Target.Cells.Count > 1 Then Exit Sub
'Dont do anything if cell is in column A
If Not Intersect([A:A], Target) Is Nothing Then Exit Sub

'Test if cell to the left is empty
If IsEmpty(Cells(Target.Row, Target.Column - 1)) Then
'Turn off events
Application.EnableEvents = False
'Clear contents if cell to the left is empty.
Target.ClearContents
'Turn events back on
Application.EnableEvents = True
'Produce message
PROMPT = "Data must be entered consecutively ie column B must be completed before data is entered column C"
Title = "Data Error"
MsgBox PROMPT, vbOKOnly, Title
End If

End Sub

The code above deletes an entry if the previous column is not filled in and then shows an error message to the user.

I have a user form that pastes data into the same cells, and the error message shows even if the user form pastes the data in the correct order (the message box has to clicked a number of times to close down) is there anything that can be added to the code above to only run if the entry is a manual input rather than the user form pasting data ??
 

Tony12zz

Board Regular
Joined
Feb 21, 2004
Messages
56
I think I managed to answer my own question by using

Application.EnableEvents = False


Application.EnableEvents = True


before the macro starts pasting stops the incorrect message.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,100
Members
425,258
Latest member
brentmitchell

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