如何在VBA中获取列索引文件列

本文介绍了如何在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!