Reference link: https://gewuweb.com/office365/3784.html

My goal: let Chinese college students have these office skills the moment they go out of school, so that professionals can effectively use office to serve them. Support brother Peng and cheer for yourself!

The following is a question paid by a group friend (thank you for paying for knowledge), which is shared with you here:

The above data has been simplified. Of course, there are many actual data.

Requirements: filter the data according to the categories of column A and column C and output it to the new workbook. The name of the workbook should be able to identify the data in the workbook.

For example, if there are peanuts belonging to vegetable market 1 in column A and corresponding column C, then filter the peanut data and output it to a new table. The name can be named vegetable market 1 + peanuts, so that you can know what data is in it through the name of the workbook. Vegetable market 1 also corresponds to radish data, which should also be output to a new workbook and named vegetable market 1 + radish. The data filtering of other vegetable markets is the same as above.

Is it troublesome to manually filter, copy and paste, and save as?

This time can reflect the advantages of VBA, and the VBA knowledge used is also at the entry level, so do you think it's useful to learn some basic VBA knowledge?

For students who want to further improve their Excel level, we strongly recommend that you learn the introduction course of VBA.

To get back to business, let's look at the above questions.

The final effect is as follows:

Here are two ideas:

1, According to our regular operation, filter and copy directly, create a new workbook, name it according to two filter conditions, then copy the content, save and close, and then continue (circular operation)

Write the above steps directly into VBA code.

The AutoFilter method of Range is used for filtering, that is, the automatic filtering of areas. The first parameter field of this method represents the filtering area. This is easy to determine. Criteria1 represents the first filtering value, such as vegetable market 1. You can also add the second filtering value, Criteria2, such as peanuts.

The problem is, how to determine the filter value during each cycle operation?

Some people in the group think of looping column a first, creating a new workbook and naming it according to the value of column A. when the looped value is the same as the name of the existing workbook in the folder, continue the looping. If it is different, create a new workbook and name it.

After the workbook is built, cycle out the name of each workbook, find the corresponding data in the data source according to the name of the workbook, and copy it to the worksheet of this workbook.

Of course, the above idea is also feasible. List the categories that are not repeated in column a through judgment and create a new workbook. The name of each workbook can be used as the filter value Criteria1, but our case has two filter values, column A and column C. of course, it can be solved with modifications.

If you master the dictionary technology, you can write the non duplicate values of column A and column C into the dictionary through the dictionary, and then recycle them. These only values are the screening values. The key is how to know that the second screening value is peanut or radish without cauliflower when circulating out of vegetable market 1?

We can assume that there are cauliflower. When the first screening value is vegetable market 1, the non repeated keywords in column C are screened again. Finally, the desired data can be obtained by judging whether there are records or not. Create a new workbook according to the circulating category, and then load the qualified data into it.

This is another way of thinking. Of course, the most basic methods of the above two ideas use the AutoFilter method of Range. In addition, there are other knowledge points that need to be possessed;

1. Create a new workbook, name, save and close the workbook.

2. Copy of Range area.

3. Dictionary technology.

4. Function Dir that traverses the file name.

5. If judgment statement, For loop statement and other basic statement structures.

But they are all basic knowledge, which can be understood as long as we study carefully.

Two, the first is using automatic screening, is too laggy, but only controls the basic operation with code. If the data is large, it will run very well. After all, it will continuously filter, copy, create new workbooks, renamed, open, paste, save and close operations.

My preferred method is to combine the values of column A and column C into the dictionary, so that we can know which categories there are, and avoid unnecessary screening. For example, if there is no cauliflower corresponding to vegetable market 1, it is not necessary to screen vegetable market 1, and then screen cauliflower.

Then, the data of each row is combined and written into an array, so that this array is a one-dimensional array, which can be filtered directly. The key to filtering is to split each key in the dictionary, and the desired data can be obtained after two filtering.

For example, screening vegetable market 1 will get the data of all vegetable markets 1. Screening peanuts in the screening results will get the data of peanuts in vegetable market 1. Put this data into a new workbook and continue the cycle

There are a lot of wordy words, and maybe some people can't understand it, so when learning VBA, you must write by hand, and then refer to other people's codes to summarize and absorb different ideas.

Of course, there are other ideas in this case. You can think more divergently.

The specific codes are as follows:

`Sub Filter data()` `Dim sh As Worksheet` `Dim arr, d As Object, i As Long` `Dim row1 As Long, col1 As Long, arr1(), n1 As Long, n2 As Long, str$` `Dim arr2, n3 As Long, c1$, c2$, arr3, arr4, arr5, n4 As Long, n5 As Long` `Dim pah$` `    'Close system prompt` `Application.DisplayAlerts = False` `    'Read the data area to arr in` `arr = Sheets(1).[a1].CurrentRegion` `    'Create a dictionary d，hold A Column sum C Filter conditions with non duplicate columns are merged and written to the dictionary` `Set d = CreateObject("scripting.dictionary")` `For i = 1 To UBound(arr)` `d(arr(i, 1) & "," & arr(i, 3)) = ""` `Next` `    'Connect each row of the data area together and write it into the array arr1 To filter in` `row1 = UBound(arr)` `col1 = UBound(arr, 2)` `ReDim arr1(1 To row1)` `For n1 = 1 To row1` `For n2 = 1 To col1` `str = str & "," & arr(n1, n2)` `Next n2` `arr1(n1) = Right(str, Len(str) - 1)` `str = ""` `Next n1` ` 'Split filter criteria and arr1 Filter out qualified records from` `arr2 = d.keys` `For n3 = 0 To d.Count - 1` `c1 = Split(arr2(n3), ",")(0)` `c2 = Split(arr2(n3), ",")(1)` `arr3 = Filter(arr1, c1)` `arr4 = Filter(arr3, c2)` `        'Put the qualified records into the new workbook` `ReDim arr5(0 To UBound(arr4), 0 To UBound(arr, 2) - 1)` `For n4 = 0 To UBound(arr4)` `For n5 = 0 To UBound(arr, 2) - 1` `arr5(n4, n5) = Split(arr4(n4), ",")(n5)` `Next n5` `Next n4` `        pah = ThisWorkbook.Path` `Workbooks.Add.SaveAs pah & "\" & Split(arr2(n3), ",")(0) & "+" & Split(arr2(n3), ",")(1)` `ActiveSheet.[a1].Resize(UBound(arr5) + 1, UBound(arr5, 2) + 1) = arr5` `ActiveWorkbook.Close 1` `        'empty arr3 , arr4, arr5 In case of loading data next time` `Erase arr3` `Erase arr4` `Erase arr5` `    Next n3` `'Release dictionary object` `Set d = Nothing` `    'Open system prompt` `Application.DisplayAlerts = True` `     'Activate data source table` `Sheets(1).Activate`  `End Sub`

Swipe right to see the full code.

Each paragraph of the code should be prompted. Students who understand the basics of VBA should be able to understand it.

That's all for sharing this section. I wish you all progress every day.

1

Online classes are gradually improving. Welcome to visit!

Reference source: https://gewuweb.com/sitemap.html