vba - Macro Explanation

Question

I was looking into how to create a shortcut with the help of VBA. The macro would basically allow me to filter the range based on cell's value. I found this macro on the internet

 Public Sub FilterOnCellValue()
        Dim nField As Long
        With ActiveCell
            nField = .Column - .CurrentRegion.Cells(1).Column + 1
            .CurrentRegion.AutoFilter Field:=nField, Criteria1:=.Value
        End With
    End Sub

The code works fine, but I have a hard time trying to understant how the dude create. So what I know so far is the guys is creating a variable. but for some reason the variable is not in a string format but in "long" and the code works perfectly when I filter based on text in a cell. I thought that long can only be used for numbers, correct me if I am wrong (which i think I am:)). Secondly. whats up with .column - .currentregion. cells.....+1? I real have no idea what he did there.

Would really appreciate some help here. the forum has been very useful to me so far.

Answer 1

The secret to understanding anything is to break it into parts

Anyways, the documentation of Autofilter states that

   Public Sub FilterOnCellValue()
                Dim nField As Long
                With ActiveCell
               '.Column: Column Number i.e  A=1,B=2 etc
               '.CurrentRegion.Cells(1).Column: Gets the col No of the firstcell of the data region          
               nField = .Column - .CurrentRegion.Cells(1).Column+1
               'Autofilter takes column number as the parameter 
               'see example below
               'nField: gets the col number of the filter value within the region
               ' leftmost field is 1 hence +1                  
                .CurrentRegion.AutoFilter Field:=nField, Criteria1:=.Value
                End With
            End Sub

for e.g if your data is in this format

     A      B
1    ID   Value
2    1    Apple
3    2    Orange
4    3    Banana
5    4    Apple
6    5    Banana

Lets say you click on cell B5(Apple) and run the macro then

1. The macro first gets the column number of the selected cell =2

2. checks which column the current region's (A1:B6) first cell(ID) is in =1

3. calculate value in Step 1-Step 2 to get which column the selected value lies in, with respect to the region (A1:B6) , the idea is using the .column as reference to find out the column number of selected value within the region result of the step:2-1+1=2

4. Filters the column number passed from step 3(in our example value is 2) of the region A1:B6 for the selected value(Apple)

Hope this helps