过滤代码如下:
Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlsheet As New Excel.WorksheetSet xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("d:\AAA.xls")xlApp.Visible = FalseSet xlsheet = xlBook.Worksheets(1)
xlsheet.Cells.AutoFilter 1, ">a", xlAnd我用MsgBox xlsheet.Cells(1, 1)显示的却不是我过滤的结果,而是原来(1,1)上的数据,怎样才能知道过滤结果记录的行号呢?谢谢?
Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlsheet As New Excel.WorksheetSet xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("d:\AAA.xls")xlApp.Visible = FalseSet xlsheet = xlBook.Worksheets(1)
xlsheet.Cells.AutoFilter 1, ">a", xlAnd我用MsgBox xlsheet.Cells(1, 1)显示的却不是我过滤的结果,而是原来(1,1)上的数据,怎样才能知道过滤结果记录的行号呢?谢谢?
代表对指定工作表的自动筛选。使用 AutoFilter 对象使用 AutoFilter 属性可返回 AutoFilter 对象。使用 Filters 方法可返回由各个列筛选组成的集合。使用 Range 方法可返回 Range 对象,该对象代表整个筛选区域。下例将保存当前筛选的地址和筛选条件,然后应用新的筛选。Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As StringSub ChangeFilters()Set w = Worksheets("Crew")
With w.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For f = 1 To .Count
With .Item(f)
If .On Then
filterArray(f, 1) = .Criteria1
If .Operator Then
filterArray(f, 2) = .Operator
filterArray(f, 3) = .Criteria2
End If
End If
End With
Next
End With
End Withw.AutoFilterMode = False
w.Range("A1").AutoFilter field:=1, Criteria1:="S"End Sub
要为工作表创建一个 AutoFilter 对象,必须先手动或使用 Range 对象的AutoFilter 方法打开工作表上某个区域上的筛选。下例将使用存储在上例模块级变量中的值来为“Crew”工作表恢复最初的自动筛选。Sub RestoreFilters()
Set w = Worksheets("Crew")
w.AutoFilterMode = False
For col = 1 To UBound(filterArray(), 1)
If Not IsEmpty(filterArray(col, 1)) Then
If filterArray(col, 2) Then
w.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1), _
Operator:=filterArray(col, 2), _
Criteria2:=filterArray(col, 3)
Else
w.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1)
End If
End If
Next
End Sub
Sheets("sheet1").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.Count - 1 ' Count the rows and
' subtract the header. ' The following three lines run an AutoFilter using "Cat" as the
' criteria for the first column and greater than 0 as the
' criteria for the second column.
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Cat"
Selection.AutoFilter Field:=2, Criteria1:=">0"
matched_criteria = 0 ' Set variable to
' zero.
check_row = 0 ' Set variable to
' zero.
While Not IsEmpty(ActiveCell) ' Check to see if row
' height is zero.
ActiveCell.Offset(1, 0).Select
If ActiveCell.RowHeight = 0 Then
check_row = check_row + 1
Else
matched_criteria = matched_criteria + 1
End If
Wend If row_count = check_row Then ' If these are equal,
' nothing was returned.
MsgBox "no matching data"
Else
MsgBox matched_criteria - 1 ' Display the number
' of records returned.
End IfEnd Sub
这是EXCEL中调试的,VB中把SHEET变量代入进去就可
(第一行是列标题,肯定是一样的)
Function filtercells(r As Long, c As Long) As Range
Dim rowindex
Dim ar As Range
Dim rw As Range
Dim i
i = 1
rowindex = "0,"
For Each ar In Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).Areas
For Each rw In ar.Rows
rowindex = rowindex & rw.Row & ","
i = i + 1
Next
Next
rowindex = Left(rowindex, Len(rowindex) - 1)
rowindex = Split(rowindex, ",")
Set filtercells = Sheet1.Cells(rowindex(r), c)
End FunctionSub dd()
MsgBox filtercells(2, 1)
End Sub
是不是只需在我的程序后面再加上上面hhjjhjhj(大头)的函数就可以了?