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 |