Excel Macro to extract items based on the amount

In Excel, you can use a macro to find and extract items based on amount. You can also find distinct values from a column or row within your excel spreadsheet. An easier way to achieve this is by using the Excel Advanced Filter option via a macro. Using this saves you from having to search manually. Here is what to do.

Enabling the Visual Basic Editor

First of all, let's learn how to locate and enable the VBA function in Excel.

1. Go to the main menu ribbon.

2. Click on the Developer tab.

 

 

3. Next, click on the options Macros and enter a macro name.

 

4. Afterward, click create to open the macro editor and begin your scripting.

Note, the simpler way to access the Visual Basic Editor is by pressing keyboard shortcuts Alt + F11. Doing this will launch the VBA screen.

How to create a unique list of items using macros

1. First, go to your excel worksheet where your data is.

2. Press on the Alt + F11 keys to open the VB editor window.

3. Next, go to the main menu ribbon and click on the Insert tab.

 

4. Next select Module to create a module.

5. In the open module window, copy and paste the VBA code stated below.

6. After you are done, close the VB Editor window.

7. Go back to your excel worksheet.

8. Press on the Alt + F8 keys. Afterward, select the option Create Unique List under the Macro name box.

9. Hit the Run button

Option Explicit

Sub CreateUniqueList()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
ActiveSheet.Range("B2:B" & lastrow).AdvancedFilter _
Action:=xlFilterCopy, _

CopyToRange:=ActiveSheet.Range("D2"), _

Unique:=True

End Sub

At times you may need to customize your macro code. For instance, you may need to paste unique values to another existing worksheet. Here you will use the code below;

Change ActiveSheet.Range("D2")

To Sheets("newssheet").Range("D2")

In the code above, you will change the newssheet entry to the name of the existing worksheet you want to paste your unique values.

  • At times, you may want to copy and paste your unique value into a new worksheet. To achieve this, you can use the VBA code below to paste your distinct values into a new worksheet.

Option Explicit

Sub CreateUniqueList()
Dim lastrow As Long
Dim ws As String
ws = ActiveSheet.Name
lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Sheets.Add.Name = "mysheet"

Sheets(ws).Range("B2:B" & lastrow).AdvancedFilter _

Action:=xlFilterCopy, _

CopyToRange:=Sheets("mysheet").Range("D2"), _

UNIQUE:=True

End Sub

On the entry 'mysheet' you can change it to your preferred name.

VBA used to find and extract all matching values using the advanced filter option

Here, you can use the macro code below and the following steps;

1. On your computer, open the Excel worksheet you want to extract the values.

2. Start by knowing what you want to find and store this in the find what string variable in case the string to find is not empty.

3.
Clear any previous find results.

4. Next, you will use the current region for cell B4. You can change this by clicking on the top left of your find range.

5. Next, look the findwhat is in the selected range. You will use the range. Find method

Sub findAll()

Dim findWhat As String, address As String

Dim fsr As Range, rs As Range, fCount As Long

findWhat = InputBox("Enter what you want to find?", "Find what…")

If Len(findWhat) > 0 Then

clearFinds

Set frs = Range("b4").CurrentRegion

Set rs = frs.Find(What:=findWhat)

If Not rs Is Nothing Then

address = rs.address

Do

Range("I5").Offset(fCount).Value = rs.Value

Range("J5").Offset(fCount).Value = rs.address

Set rs = frs.FindNext(rs)

fCount = fCount + 1

Loop While Not rs Is Nothing And rs.address <> address

End If

End If

End Sub