VBA Delete Rows based on drop down list selection

Ridofhate

New Member
Joined
May 5, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Dear community members,
I'm new to VBA and would like to try something in one of my excel files. I would like to have a worksheet that based on a drop down list selection only keeps the rows which are referring to this selection.

For Example:
If D4 "LOG" is selected all rows which not contain "LOG" in column "A" should be deleted.
In this case the rows 120 - 142 would have been deleted (if Range is starting from "A74").

Do you have a good idea for a code?

Thank you very much! :)

2021-05-05_08h53_23.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Ridofhate

New Member
Joined
May 5, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
With the following code i worked to delete the rows. Is there also a possibility, that the way of selection is changed to "contain"? Means that a cell in Column A can contain several functional area and when it contains something from the drop down list it won't be deleted.

Thank you very much!

VBA Code:
Sub Selection()
With Sheets("Budget")
    .Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:="<>" & .Range("D4").Value
    .Range("A8").CurrentRegion.Offset(1).EntireRow.Delete
    .AutoFilterMode = False
End With
End Sub
 

Ridofhate

New Member
Joined
May 5, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Trackback. Is there someone who can help regarding this "contain" question?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,082
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Criteria1:="<>*" & .Range("D4").Value & "*"
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,082
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,136,641
Messages
5,676,954
Members
419,663
Latest member
Xbox_360

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