Row Deletion

S

surveyorinva

Thanks for all the help everyone has given so far and hope someone can help with this question
Background
I have sorted some data into four different columns and have multiple rows. In the fifth column (E) I have a command to return a true statement if the data in A1 matches the value anywhere in column D. In the sixth column (F) I have a command to return a true statement if the data in D1 matches any where in column A. In the seventh column (G) I have an if statement with three different outcomes for column F&G (True-True returns 1, True-False returns 2, & False-False returns a 3).
Question
What would be the way to have Excel search through Column G and if a 3 is returned, clear the data for columns A through D for just that row?
Any help would be great!
 
D

Dave Newing

Hi,

You can only really do this with a bit of code. I'm not sure whether you want to delete the data in the CELLS in just Columns A to D (Sub Ex1), or whether you want to delete the entire ROW (Sub Ex2) when a '3' is found.

Here are the 2 examples:

Sub Ex1()
Dim r As Long
Range("G1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 3 Then
r = ActiveCell.Row
Range("A" & r & ":D" & r).Value = ""
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub


Sub Ex2()
Dim r As Long
Range("G1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 3 Then
r = ActiveCell.Row
Rows(r).Delete Shift:=xlUp
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

To run these, copy them into a module in Visual Basic (Press 'Ctrl+F11' when in Excel to open. Choose 'Insert' then 'Module').

Hope this helps

Regards

DN
 
Top