VBA Delete entire Rows If Not .....

[stuartgb100]

Hi,

User selects the following entire rows ( 3, 5 ,7, 12 for example) in a sheet and then deletes them either via keyboard Delete or mouse right click.

Is there a way to check the contents of column L in each of those rows for the value "Keep" and only allow the row deletion if "Keep" is not present ?

Thanks.

 

[My Aswer Is This]

We can write a vba script and delete all rows in your sheet if the word "Keep" is not in column "L"

Is that what you want. To activate a script you must click a button or use a shortcut key.

 

[My Aswer Is This]

If you want all rows deleted if they do not have "Keep" in column "L" then use the below script.

Sub FilterMini()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "L").End(xlUp).Row
Dim Keep As String
Keep = "Keep"
With ActiveSheet.Range("L1:L" & Lastrow)
.AutoFilter Field:=1, Criteria1:="<>Keep"
.SpecialCells(xlCellTypeVisible).Rows.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[stuartgb100]

Thanks for the replies.

This is a fuller explanation of my situation:

User creates a data set in worksheet("B Sheet") as follows:
1. they enter their data in A10, B10 and D10.
2. when they click on C10 they are taken to a cell on worksheet("D Sheet) where they enter numerical data.
3. calculations take place automatically and the result is passed back to C10 on worksheet("B Sheet")
4 the value in C10 takes the form ='D Sheet'!D27
5. in addition, a reference is passed back to L10 in "B Sheet" to show where the calculations took place in "D Sheet".
6. so l10 in "B Sheet" will have a value in the form ='D Sheet'!A30 for example.

I wish to remove the calculations associated with the data in row 10 before deleting row 10

Thanks again.

 

[My Aswer Is This]

This is now beyond my understanding.

Maybe someone else here at Mr. Excel will be able to help you.