如何在VBA中获取列索引文件列 [英] How to get column index fo filered column in VBA

问题描述

大家好，



如何在excel VBA中准确获取过滤列的列索引和过滤值。

（获取索引和事件表中的值激活）

我试过但不完全正确，请帮帮我



提前致谢

Hi All,

How to get exactly column index and filter value of filtered columns in excel VBA.
(get index and value in event Sheet activate)
I tried but it don''t exactly, Please help me

Thanks in advance

推荐答案

MS Excel 2003解决方案



步骤：

1）添加新模块（不要更改名称： Module1 ）

Solution for MS Excel 2003

Steps to do:
1) Add new module (do not change it name: Module1)

Option Explicit

Public ExcApp As MyApp

2）将以下代码添加到 ThisWorkbook 模块：

2) Add below code to ThisWorkbook module:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set ExcApp = Nothing
End Sub

Private Sub Workbook_Open()
Set ExcApp = New MyApp
End Sub

3）添加新的类模块（将其名称更改为： MyApp ）

3) Add new Class Module (change it name to: MyApp)

Option Explicit

Public WithEvents oApp As Application


Private Sub Class_Initialize()
Set oApp = Application
End Sub

Private Sub Class_Terminate()
Set oApp = Nothing
End Sub

Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng As Range, wsh As Worksheet, af As AutoFilter, f As Filter, i As Integer
Dim sTmp As String

On Error Resume Next

Set rng = Target
Set wsh = rng.Parent
Set af = wsh.AutoFilter

sTmp = "Filter range: " & af.Range.Address & vbCr
For Each f In af.Filters
    i = i + 1
    If f.On Then
        sTmp = sTmp & "Filter on: " & af.Range.Cells(af.Range.Rows(1).Row, i).Address & vbCr
        sTmp = sTmp & "Criteria 1: " & f.Criteria1 & vbCr
        sTmp = sTmp & "Operator: " & f.Operator & vbCr
        sTmp = sTmp & "Criteria 2: " & f.Criteria2
    End If
Next

If sTmp<>"" Then MsgBox sTmp, vbInformation, "Filter information..."

Set f = Nothing
Set af = Nothing
Set wsh = Nothing
Set rng = Nothing

End Sub

4.保存工作簿，关闭它并再次打开启用宏选项。



这就是全部！

4. Save workbook, close it and open it again with "Enable macros" option.

That''s all!

这篇关于如何在VBA中获取列索引文件列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！