VBA Delete entire Rows If Not .....

stuartgb100

Active Member
Joined
May 10, 2015
Messages
287
Office Version
  1. 2021
Platform
  1. Windows
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.
 

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
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.
 
Upvote 0
If you want all rows deleted if they do not have "Keep" in column "L" then use the below script.

Code:
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
 
Upvote 0
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.
 
Upvote 0
This is now beyond my understanding.

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

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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
Back
Top