Excel列自动过滤使用VBScript [英] Excel column autofiltering using VBScript

问题描述

假设我有一个矩阵如下：

  EMPID工作类型
＃1 10T
＃2 11P
＃3 20P
＃4 40T
＃5 50T
＃6 15P
＃7 19T
  

After Filtered

  EMPID工作类型
＃2 11P
＃3 20 P
＃6 15P
  

你能建议我怎么做在vbscript？我知道VBA的语法，但是在VBScipt中不知道如何格式化？

过滤后如果我在列＃1上使用CountA， code> 3 或 7 ？太多困惑我在这里。

更新

  objSheet2.Range（B1）。AutoFilter 2，Parent,, ，False 
  

删除自动过滤我使用的代码 - objSheet2.Rows（1：1）。Select.AutoFilter 但收到错误无法获取范围类

已更正 objSheet2.Range（B1）。AutoFilter 2， Parent,,, True
objSheet2.AutoFilterMode = False

查询： 一旦行过滤，那么当循环过滤数据行时，如何识别过滤数据行的实际行数？

ANSWER

 对于rowIndex = 2 To objExcel1.Application.WorksheetFunction.CountA（ob1.Columns（1））
如果objSheet2。 Rows（rowIndex）.Hidden Then 
'do nothing  -  row is filtered out 
 Else 
 MsgBox（rowIndex）
 End If 
 Next 
  

这样我得到了Filtered行实际的行号。如果有任何更好的想法你们发布，请咨询： - ）

屏幕截图

谢谢

解决方案

我认为这将让你想要一点点快一点。

  objSheet2.Range（B1）。AutoFilter 2，Parent,,, True 
 
 Dim rngFilter as Range 
设置rngFilter = objXL.Application.Intersect（objSheet2.UsedRange，objSheet2.UsedRange.Offset（1），objSheet2.Columns（1））。SpecialCells（xlCellTypeVisible）
 
 msgbox过滤范围有& rngFilter.Rows.Count& 行。 
'根据您的需要与范围。 
 
 Dim cel as objXL.Range'or just leave this off 
对于rngFilter中的每个细胞
 msgbox cel.row 
下一个
 
 objSheet2.AutoFilterMode = False 
  

这样，您只能在过滤后选择可见单元格。然后，您可以根据需要将该范围加载到数组或循环中。

Suppose I have a matrix like below :

     EMPID    Type Of employment
   #1 10            "T"
   #2 11            "P"
   #3 20            "P"
   #4 40            "T"
   #5 50            "T"
   #6 15            "P"
   #7 19            "T"

After Filtered

    EMPID    Type Of employment
  #2 11            "P"
  #3 20            "P"
  #6 15            "P"

Can you people suggest me How to do it in vbscript? I know the syntax of VBA,but in VBScipt no idea how to format?

And after filtering If I use CountA on column#1 should I get the Row numbers as 3 or 7?Too much confused I am here.

UPDATE

  objSheet2.Range("B1").AutoFilter 2, "Parent",,,False

to remove auto-filtering i used the code - objSheet2.Rows("1:1").Select.AutoFilter but getting an error Unable to get the select property of the Range Class

Corrected objSheet2.Range("B1").AutoFilter 2, "Parent",,,True objSheet2.AutoFilterMode = False

Query: once the rows are filtered,then how would I recognize the actuall row numers of the filtered data rows,when looping over them?

ANSWER

    For rowIndex=2 To objExcel1.Application.WorksheetFunction.CountA(ob1.Columns(1))
    If objSheet2.Rows(rowIndex).Hidden Then
        ' do nothing - row is filtered out
    Else
        MsgBox(rowIndex)
    End If
    Next 

This way I got the Filtered rows actual row number. Please advice if any better idea you guys post:-)

Screen shot

Thanks

解决方案

I think this will get you want you are after a little faster.

objSheet2.Range("B1").AutoFilter 2, "Parent",,,True 

Dim rngFilter as Range
Set rngFilter = objXL.Application.Intersect(objSheet2.UsedRange,objSheet2.UsedRange.Offset(1),objSheet2.Columns(1)).SpecialCells(xlCellTypeVisible)

msgbox "Filtered range has " & rngFilter.Rows.Count & " rows."
'do with the range as you wish.

Dim cel as objXL.Range 'or just leave this off
For each cel in rngFilter
   msgbox cel.row
Next

objSheet2.AutoFilterMode = False

In this way you are only selecting the visible cells after being filtered. You can then load that range into an array or loop through it as you see fit.

这篇关于Excel列自动过滤使用VBScript的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！