VBA code to filter data

[STEVENS3010]

Hi all,

I have a sheet with 5 columns of data that I need filter one column for a a specific value. I've used the macro recorded to help me; the script is below...

Selection.AutoFilter
    ActiveSheet.Range("$A$2:$E$5654").AutoFilter Field:=3, Criteria1:=Array( _
        "AWAIT DIP UNDERWRITING"), _
        Operator:=xlFilterValues

My questions are, based on the above script, am I correct in assuming it will only filter the range between A2:E5654? If so, as the number of rows of data will be changing, is it possible to code the script to run between columns A and E, but to automatically look for the last row, rather than specifying a particular row number?

My second question then was around copying and pasting the filtered data to a new sheet. Is it possible? If so, is it clever enough to only copy and paste the filtered data, even if this changes every time the filter is applied?

If anybody could help I'd really appreciate it.

Thanks.

 

[Fluff]

Assuming you have a header row in row 2 & there are no blank rows in the data, you can use

Sub stevens()
   With ActiveSheet
      .Range("A2:E2").AutoFilter 3, "AWAIT DIP UNDERWRITING"
      .AutoFilter.Range.Offset(1).Copy Sheets("Sheet2").Range("A2")
   End With
End Sub

 

[STEVENS3010]

Assuming you have a header row in row 2 & there are no blank rows in the data, you can use

Sub stevens()
   With ActiveSheet
      .Range("A2:E2").AutoFilter 3, "AWAIT DIP UNDERWRITING"
      .AutoFilter.Range.Offset(1).Copy Sheets("Sheet2").Range("A2")
   End With
End Sub

This is brilliant, thanks so much. One thing I did forget to ask was whether it was also possible to sort the filtered data by alphabetical order for column E?

 

[Fluff]

Just turn on the macro recorder & do it manually, then you will have the code.

 

[STEVENS3010]

good shout!

 

[Fluff]

You're welcome

 

[STEVENS3010]

Hi, me again.

If I were to use the below code to filter for more than one value e.g. "Await DIP Underwriting", "Await application" etc... how would I need to amend the code?

Assuming you have a header row in row 2 & there are no blank rows in the data, you can use

Sub stevens()
   With ActiveSheet
      .Range("A2:E2").AutoFilter 3, "AWAIT DIP UNDERWRITING"
      .AutoFilter.Range.Offset(1).Copy Sheets("Sheet2").Range("A2")
   End With
End Sub

Thanks

 

[Fluff]

How about

Sub stevens()
   Dim Ary As Variant
   
   Ary = Array("AWAIT DIP UNDERWRITING", "Await application", "Something else")
   With ActiveSheet
      .Range("A2:E2").AutoFilter 3, Ary, xlFilterValues
      .AutoFilter.Range.Offset(1).Copy Sheets("Sheet2").Range("A2")
   End With
End Sub

 

[STEVENS3010]

Really appreciate you helping me out again - thanks so much.

I've just added the code and amended some of it to add addtional values and it keeps coming up with an error... THis part of the code seems to be the issue?

   Ary = Array("APPLICATION RECEIVED", "AWAIT CREDIT SANCTION", "AWAIT CREDIT UNDERWRITING", _
    "AWAIT INSTRUCT SURVEY, "AWAIT PRESENT FOR UNDERWRITING", _
   "AWAIT SURVEY REPORT", "AWAIT UNDERWRITING")

I'm guessing I'm doing something wrong but I don't know what exactly?

 

[Fluff]

You're missing a "

"AWAIT INSTRUCT SURVEY",