Creating macro to search two values and delete them

Closed
Report
Posts
2
Registration date
Sunday May 15, 2016
Status
Member
Last seen
May 15, 2016
-
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
-
Hello,


I´m new here. but I´ll try to do my best.
I am looking for a macro where I can search in the column "I" all the values between two values. Those values are located in the cells F1 and F2, and my sheet is named Hoja1. I need this really urgent to my work.

Thanks in advance for your answer!

1 reply

Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
Hello Ricardo,

Following is some code that should do the task for you:-


Option Explicit
 
Sub FindStuff()

Application.DisplayAlerts = False

        Dim val1 As Long: val1 = [F1]
        Dim val2 As Long: val2 = [F2]
        Dim lr As Long
        
lr = Range("I" & Rows.Count).End(xlUp).Row

        Range("I1:I" & lr).AutoFilter 1, ">=" & val1, xlAnd, "<=" & val2
        Range("I2:I" & lr).Delete
        [I1].AutoFilter
        Range("F1:F2").ClearContents
        
Application.DisplayAlerts = True

End Sub


Following is the link to my test work book. Click on the run button to see it work:-

https://www.dropbox.com/s/v065miwqw7m2spy/Ricardo%28Find%20all%20values%20between%202%20values%2C%20AutoFilter%29.xlsm?dl=0

I have just used numbers as the values in the test work book.
The criteria values in F1 and F2 are also cleared ready for new values.

I hope that this helps.

Cheerio,
vcoolio.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month