精华内容
下载资源
问答
  • 筛选之后,我需要选中O10单元格,填充公式(但是因为文档每次都一样,筛选内容的变化,下次可能就在O10单元格了,所以需要用到宏代码) j = Rows("2:3000").SpecialCells(12).Row MsgBox (j) 返回结果是10 ...

     2020-5-26更新:可以不限定3000,使用通用代码:Rows.Count,这样在office07之后的版本,以及07之前的版本,不管有多少行,都可以正常使用~

    Sub test()
        MsgBox (Rows("2:" & Rows.Count).SpecialCells(12).Row)
    End Sub

    如图所示,在网络抓取的数据,需要进行清洗,在清洗的过程中,需要用到筛选,

    筛选之后,我需要选中O10单元格,填充公式(但是因为文档每次都不一样,筛选内容的变化,下次可能就不在O10单元格了,所以需要用到宏代码)

    j = Rows("2:3000").SpecialCells(12).Row
    MsgBox (j)

    返回结果是10

    上面的2:3000根据个人需求修改(不能直接写rows(),这样的话,因为第一行有标题,所以返回的就是1)

    如此,就可以返回我第一个筛选之后,第一个可见单元格的行号。这样就可以用cells进行定位写入公式了~

    如下为拓展文档:

     

    vba SpecialCells(Type,Value) 参数说明

    Range.SpecialCells 方法可以返回一个 Range 对象,该对象代表与指定类型和值匹配的所有单元格。 其语法如下: 表达式.SpecialCells(Type,Value) 其Type参数的可选XlCellType常量及对应值如下表:

    XlCellType 常量

    说明

    xlCellTypeAllFormatConditions

    -4172

    任意格式单元格

    xlCellTypeAllValidation

    -4174

    含有验证条件的单元格

    xlCellTypeBlanks

    4

    空单元格

    xlCellTypeComments

    -4144

    含有注释的单元格

    xlCellTypeConstants

    2

    含有常量的单元格

    xlCellTypeFormulas

    -4123

    含有公式的单元格

    xlCellTypeLastCell

    11

    已用区域中的最后一个单元格

    xlCellTypeSameFormatConditions

    -4173

    含有相同格式的单元格

    xlCellTypeSameValidation

    -4175

    含有相同验证条件的单元格

    xlCellTypeVisible

    12

    所有可见单元格

    当Type参数为 xlCellTypeConstants 或 xlCellTypeFormulas时,Value参数有4种XlSpecialCellsValue 常量可选,见下表:

    XlSpecialCellsValue 常量

    说明

    xlErrors

    16

    错误值

    xlLogical

    4

    逻辑值

    xlNumbers

    1

    数字

    xlTextValues

    2

    文本

    如下SpecialCells(xlCellTypeConstants,23)语句中的23=16+4+1+2,即表示定位的常量包含4种类型的单元格:错误值+逻辑值+数字+文本

    1. Sub 定位常量()
    2. Range("a:a").SpecialCells(xlCellTypeConstants, 23).Select
    3. End Sub

    如只要定位常量错误值及逻辑值(即16+4=20),则采用如下代码:

    1. Sub test1()
    2. Range("a:a").SpecialCells(xlCellTypeConstants, 20).Select
    展开全文
  • vba autofilter 筛选

    千次阅读 2019-08-26 21:29:13
    国外网站介绍VBA autofilter 很不错 https://trumpexcel.com/vba-autofilter/#Example-Multiple-Criteria-With-Different-Columns 说实话国外的网站对于VBA的资料相...1 显示所有数据,隐藏数据 (仅在Autofilte...

    国外网站介绍VBA autofilter 很不错

    https://trumpexcel.com/vba-autofilter/#Example-Multiple-Criteria-With-Different-Columns

    说实话国外的网站对于VBA的资料相对于国内来说内容更丰富更具有实用性。当然是Bing搜索引擎与百度引擎相比较。

    1  显示所有数据,不隐藏数据 (仅在Autofilter模式下使用)

    If Worksheets("Function").FilterMode = True Then
        
    Worksheets("Function").AutoFilter.ShowAllData
    Worksheets("Function").Rows.Hidden = False
        
    End If

    但是如果是需要VBA高级筛选的话就不能用AutoFiler.ShowAllData 方法,否则报错。因为这个方法只针对AutoFilter情况。可以换成Worksheets("XXX").ShowAllData 

     

     

    2 多条件筛选(仅限于两个筛选条件,如果多条件必须是高级筛选)

    With w.Range("A1")
    .AutoFilter Field:=2, Criteria1:= _
            Array("Teller", "AIO","Plan"), Operator:=xlFilterValues
    .AutoFilter Field:=7, Criteria1:="<>t*", Operator:=xlFilterValues
    End With

    其实有很多博客都写了关于autofilter的函数方法,包括参数用途。但是这里我还是最终想讲一下通配符的作用,没错就是“*”,“?”,“~”。这个坑的我好苦啊(应该是寻找答案的时间比较长,2天时间)

    先简要介绍一下这三种通配符。

    通配符意义
    ~类似于转义字符“\” 如果匹配的字符串中含有特殊字符,在特殊字符前加“~”
    代表一个字符
    *代表一个或者多个字符

    我的需求是第七列(也就是G列)筛选出不含有开头为字母t的内容。一开始以为Criteria筛选可以用正则表达式进行筛选,结果多次尝试后根本就不行,然后无意间找到通配符这个功能,研究了一下,好像也不可以。VBA script regular express 引用也不可以。可能是想得太多了,下班在车上突然想到用<>也就是不等于试一试,没想到成功了。VBA的正则还是有一些不同的。我要好好记住它。

    Criteria1:= "<>t*"

     

     

    3 高级筛选

    国外有一个网站写的很好,不得不说国外的资料比较全。这是因为什么呢?我认为是他们很认真,国内就恐怕做不到。谢谢这位作者。

    https://www.thespreadsheetguru.com/blog/2015/2/16/advanced-filters-with-vba-to-automate-filtering-on-and-out-specific-values

    上面网站讲述了一个很好的例子。具体情况具体分析。

    高级条件筛选

    首先要创建一个筛选标准的Excel。如果是逻辑与的话,需要在同一行。反之逻辑或则是非同一行。如下

    Assigned ToAssigned ToAssigned ToSub Category
    <>t*<>y*<>io*01.  Test1
    <>t*<>y*<>io*01.  Test2

     

    说明一下"<>"这个符号在Like 函数中无法使用,但是可以再高级筛选中使用。

    https://docs.microsoft.com/zh-cn/office/vba/language/reference/user-interface-help/like-operator

    内置模式匹配为字符串比较提供了一个功能多样的工具。 利用模式匹配功能,您可以将通配符、字符列表或字符范围任意组合使用来匹配字符串。 下表显示了 pattern 中允许使用的字符以及它们的匹配项:

    _模式_中的字符_字符串_中的匹配项
    ?任意单个字符。
    *零个或多个字符。
    #任何单个数字 (0-9)。
    charlist ]charlist 内的任意单个字符。
    !charlist ]charlist 外的任意单个字符。

    Code

    w.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
         Sheets("Critical").Range("A1").CurrentRegion, Unique:=True

     

    展开全文
  • 今天和大家要说的是VBA数组的应用,上篇文章我们说的是数组的基础知识点,有明白的小伙伴可以点击链接进行知识点回顾Excel VBA 数组基础知识,初学者可不学的关键知识,今天主要说说数组的几操作方法,这是...

    今天和大家要说的是VBA数组的应用,上篇文章我们说的是数组的基础知识点,有不明白的小伙伴可以点击链接进行知识点回顾Excel VBA 数组基础知识,初学者不可不学的关键知识,今天主要说说数组的几个操作方法,这个是我们以后会经常遇到的,也是经常会使用的方法。

    数组的合并和字符串拆分(Join & Split)

    在Excel的数据处理时候,我们经常会遇到拆分和合并的操作,可能你在Excel中的基础操作都会,但是我们如果要操作数组呢?数组是在我们内存之中的,我们无法再Excel中可以看到,除非是在本地窗口中可以看到。

    一起来看看例子:

    拆分字符串 split(字符串,"分隔符")

    用分隔连接数组的每个元成一个字符串 join(数组,"分隔符")

    ed8b156f01f93b52705e7b95943b8965.png

    是不可以看到这2个函数的用法其实很简单,他们的作用是相反的,一个是合并,一个拆分,天下分分合合,就在你和我之间。有么有喜欢上这对冤家?

    但是需要注意的一点是,他们都只能操作一维数组,这个是比较限制的。如果们在二维数组时候,想使用个方法,我们应该第一步转化成一维数组,然后在操作即可。

    Filter实现数组筛选

    其实数组的筛选就是根据我们指定的条件,从数组中筛选出来符合我们指定条件的值,然后在组成一个新的数组。这个操作你肯定不陌生,如下图所示,我们在Excel中的常规操作:

    250311989c89e147400487bb93a9bbe7.png

    但是今天我们猪脚是在数组中进行帅选,我们用的是Filter函数,用法是:Filter(数组, 筛选的字符, 是否包含)

    我们可能只说使用方法,其实你还是不太明白,我们举一个例子,你看了就明白,如需他所示:

    3b363ef8e9c12c5e677baacc2b00e528.png

    Arr是我们利用常数数组进行赋值的数组,arr1是我们帅选包括字符”A”的新组成的一个数组,arr2,是我们帅选出来不包括“A”的新数组。不知道你发现没有,我们在数组中使用这个函数的时候,只能模糊帅选,并不能精确进行帅选,这个请务必注意一点。

    取出数组中的最大值和最小值(Max和Min)

    在Excel中我们取出最大值和最小值,我们都是用的MAX和MIN函数,其实这2个在我们的数组中,亦可使用,具体使用方法如下所示:

    353f92046ecc6891c0c4458823a8a0b3.png

    顺便再说一下,我们也可以取出数组的第几大值和几小值,如下:

    b2dda1dbcadfdf2b62278c6ff042af4a.png

    这个方法在我们统计数据的时候,经常会用到。

    数组求和

    在数组中,我们同样可以求和,同样也是借用工作表函数SUM来完成,我们一起看看:

    f33f86bf8f7e10e76427f63054583bd7.png

    是不是发现,数组怎么这么好用?其实数组操作还不止这些,我们在继续看看:

    数组的计数Count和Counta

    Count和Counta可以统计数组中数字的个数和数字+文本的个数。如下图所示:

    e7c7c21dc9bd3c1ccff39e22e645656f.png

    Count和Counta在数组中和Excel中的使用方法是一样的,是通用的。

    数组的查询和拆分

    假如我们已经有了一个数组,但是我们想查询其中的某一个值,通过帅选只能模糊匹配,那我们有没其他的方法呢?如下:

    1b8baddf11cf796cbca25c0cef66aa02.png

    数组从二维拆分成一维,你是否还记得我们上篇文章说的?

    我们可以借用工作表函数index达到按列拆分数组,即多列构成的数组,你可以任意拆分出一列构成新的数组。方法是:Application.Index(数组, , 列数) :

    2eebd387653dace6d828f9fcc978624b.png

    数组拆分其实在VBA中间很难操作,有时候我们是借用API函数,或者说这个函数有多少人知道呢?所以我们一般记下index函数拆分数组即可:

    数组维数的转换

    今天说的最后一个,数组的维数的转变。这个也可借用工作表函数来完成这个,我们一起来看看吧 !

    8a3475dd285cf196330ad11494ebde43.png

    在转置时只有1列N行的数组才能直接转置成一维数组,这个请注意一下,否则会报错的。

    其实我们很多的工作表函数都可以在我们的Excel中进行使用,我们可以自行尝试一下,看看实际的效果。

    以上就是我们今天和大家说的代码的中的数组常用技巧,其实VBA代码学习起来非常简单,他就是一个脚本语言的存在,他有自己的条条框框,你按照他的游戏规则来写代码就可以。如果有不明白的或者不懂的可以在下方留言,我们会一一解答的。

    be4144d3758b2910be529da96e79f10b.png

    我是Excel教案,关注我持续分享更多的Excel技巧!

    展开全文
  • 数据中有个字段,有多个条件需要不包含情况。可criteria1和criteria2只能选择两个不包含情况。 我也试过用array(<>*)进行处理,可是没有效果。 (制作宏的过程没办法模拟出这种情况) 请问这种情况该如何编写...
  • 大家好,我们今日继续讲解VBA数组与字典解决方案第22讲:在VBA中如何使用动态数组,以及利用动态数组去除重复值的方法。...一 :Filter函数:这函数返回一下标从零开始的数组,该数组包含基于指定筛选条件的...

    大家好,我们今日继续讲解VBA数组与字典解决方案第22讲:在VBA中如何使用动态数组,以及利用动态数组去除重复值的方法。如果文本中含有大量的重复值,此时,如果我们要剔除重复值,该怎么办?用VBA的方法该如何做到呢?我在这讲和下一讲中将解答这个问题,并提供给读者一个可以测试的实例。今日先讲这个内容要用到的知识点。

    一 :Filter函数:这个函数返回一个下标从零开始的数组,该数组包含基于指定筛选条件的一个字符串数组的子集,语法如下:Filter(sourcesrray, match[, include[, compare]])

    参数

    a) sourcesrray是必须的,要执行搜索的一维字符串数组。

    b) match是必须的,要搜索的字符串。

    c) include是可选的,Boolean值,表示返回子串是否包含match字符串。如果参数include是True,Filter函数返回的是包含match参数子字符串的数组子集。如果参数include是False,Filter函数返回的是不包含match参数子字符串的数组子集。

    d) compare是可选的,所使用的字符串比较类型。

    二:ReDim语句,这个语句在过程级别中使用,用于为动态数组变量重新分配存储空间,语法如下:

    ReDim [Preserve] varname(subscripts) [As type] [, varname(subscripts) [As type]]

    参数

    a) Preserve是可选的,关键字,当改变原有数组最末维的大小时,使用此关键字可以保持数组中原来的数据。

    b) varname是必须的,变量的名称。

    c) subscripts是必须的,数组变量的维数,最多可以定义 60 维的多维数组,使用下面的语法;[lower To] upper [,[lower To] upper]

    使用动态数组去除文本中的重复值,如下面的代码所示。

    Sub MyNZsz_5() '第22讲 利用数组排重的方法

    Dim Splarr() As String

    Dim Arr() As String

    Dim Temp() As String

    Dim r As Integer

    Dim i As Integer

    On Error Resume Next

    Splarr = Split(Sheets("22").Range("a1"), " ")

    For i = 0 To UBound(Splarr)

    Temp = Filter(Arr, Splarr(i))

    If UBound(Temp) < 0 Then

    r = r + 1

    ReDim Preserve Arr(1 To r)

    Arr(r) = Splarr(i)

    End If

    Next

    Sheets("22").Range("a5").Resize(r, 1) = Application.Transpose(Arr)

    End Sub

    代码解析:

    MySplitarr过程将工作表Sheets("22")中A1单元格的文本去除重复值后写入到工作表Sheets("22")中的A列单元格。

    第2行代码声明数组Splarr用来保存Sheets("22")中A1单元格的文本。

    第3行代码声明数组Arr用来保存去除重复值后的文本。

    第4行代码声明数组Temp用来判断文本是否重复。

    第5行代码声明变量r用来保存去除重复值后的文本数量。

    第7行代码启动错误处理程序来忽略错误,因为在程序运行到第11行代码会发生下标越界错误。

    第8行代码使用Split 函数以Sheets("22")中A1单元格的文本创建一个下标从零开始的一维数组。

    第9行代码使用For...Next语句遍历数组Splarr的所有元素。

    第10行代码使用Filter函数创建一个数组Temp用来保存以当前Splarr数组的值在Arr数组中的搜索结果。

    第11行代码根据返回的数组Temp的最大下标来判断当前Splarr数组的值是否重复。在使用使用Filter函数时如果没有相匹配的值,将返回一个空数组,最大下标小于0。如果没有找到,返回的结果是-1.

    第12行代码如果当前Splarr数组的值不重复则将变量r的值加1。

    第13行代码重新定义动态数组大小。

    第14行代码将不重复值添加到数组Arr中。

    第15行代码使用工作表Transpose函数将去除重复值的的文本转置后写入到工作表单元格中。

    代码的截图:

    d747003cdace1f1ba40cecc167fc8873.png

    运行前的截图:很显然有很多的重复数值:

    d11ce6b7887f1546c37cd1f7c697cbc9.png

    运行后的截图:

    0aae4e6bbfe01274cf2bc4527f3ea5df.png

    今日内容回向:

    1 上述代码是否清楚了它的运行过程呢?

    2 第7行代码启动错误处理程序来忽略错误,这是为什么呢?

    展开全文
  • word vba 如何查找出一字符串中包括了多少指定子字符串? Sub at() aaaa = "ASDFSF。ASDF。SDF。234。'XCV。d" '第1种方法 bbb = Replace(aaaa, "。", "") cccc = Split(aaaa, "。") CS1 = Len(aaaa) - Len...
  • 今日继续讲解VBA数组与字典解决方案,的内容是第10讲,今日继续给大家讲解数组及数组公式结果的制约性和集合性,上节讲到截图的结果返回的是3,而不是0!这是为什么呢?我们仍然看下面的截图:再次回顾E5的公式; “=...
  • 在满足以下两条件时,借助 VBA 可以极大的提升生产率,降低出错率:你的电脑上允许自主安装软件; 你需要执行的工作中大部分的步骤都是固定且重复的。项目背景近期接到一工作任务,在运行设备中,会定期生成...
  • 大家好,我们今日继续讲解VBA代码解决方案的第61讲内容:在VBA中如何使用动态数组,以及利用动态数组去除重复值的方法。在上一讲中我们讲了使用数组...我在这讲和下一讲中将解答这问题,并提供给读者一可以测试...
  • 如图所示,本例希望能够从员工列表中随机抽取5名员工的记录,该如何用VBA实现? 员工编号 姓名 部门 员工编号 姓名 部门 0006 程建华 管理部 0314 张宏 销售部 0009 ...
  • 函数作用:在多个工作表中查找一个范围内符合某个指定条件的项目对应指定范围加总求和..........................59 '35.函数作用:返回 Column 英文字.......................60 '36.函数作用:查找指定列名的列数.......
  • vba自动筛选数据透视表 自动更新特定的数据透视表 (Update Specific Pivot Tables Automatically)In Excel 2010, you can use Slicers to change multiple pivot tables. However, you might be working in an ...
  • 由于数据透视表的字段能全部取消筛选(至少要保留一项),所以先找到红色内的第一有效值(本例中的红字本来就是数据源,所以都是有效的,实际使用可能会有在列表的内容),让数据透视表只显示这一项 ...
  • VBA实现Excel模块筛选高级应用场景!

    千次阅读 2019-09-02 13:34:33
    做过项目管理或者产品管理的小伙伴...今天我给大家讲一复杂场景下通过VBA实现Excel模块筛选的高级应用。 这里我们先通过图片和文字描述一下需要实现的一需求场景: 首先有一Excel,其中有两Sheet,Sheet1...
  • End Sub 如果一个内容里面涉及到多个符合条件的值,且都需要输出的话,需要加match参数用来遍历MatchCollection Sub reg() Dim reg As New RegExp Dim m As Match Dim mc As MatchCollection Dim myMail As String ...
  • 针对一工作簿中很的表,有些需要隐藏,有些需要显示出来,因表的名称比较统一,仅有主体名称不同;如图: 目的是实现仅显示“汇总表”和 “人工费用表”,其他表则都隐藏 二、代码示例 Sub 特定...
  • 现希望制作一个包含有文本框的浮动工具栏,当在文本框中输入字符并按【Enter】键后,对该表进行模糊查找,将该表中含有该字符的所有记录隐藏。 序号 姓名 科室 级别 身份 职称 受聘专业 ...
  • 将Excel数据筛选多个工作表 (Filter Excel Data Onto Multiple Sheets) There is a sample Excel file on the Contextures website that has a list of orders, and sales rep names. It has a macro to filter ...
  • Sub 工作表拆分2() '通过筛选方法完成需求,速度快,但当有合并单元格时就能用。读者可以根据实际情况选用 Dim SplitCol As String, ColNum As Integer, HeadRows As Byte, arr, lastrow, i, ShtIndex, only As ...
  • 工作中,需要次用到“自定义自动筛选方式”对话框,所以想到通过编写宏代码,以提高工作效率。在Excel Home上([如何用宏自定义自动筛选方式的快捷键](http://club.excelhome.net/thread-1117437-1-1.html ""))找到...
  •  某EXCEL文件中有两个(其实是多个,但需要用的就两个)如下图:   同学的领导要求,根据选中的名称显示出这个单位的所有手机号码(为保护隐私用*号替换了)及相关信息。说白了就是表(正常 集团 (2))里的...
  • 想用vba选中一列中第一有值的单元格将其填色,现在不知如何选中,求助</p>
  • 在Excel的使用过程中,经常需要引用其他工作簿的数据,而用户往往希望能在打开工作簿或看似打开工作簿的情况下取得其他工作簿中的数据,有以下几种方法可以实现。 1、使用公式 如果需要引用的数据不是太,...
  • 接到一任务,由于数据操作人员不会使用编辑公式进而无法进行相关筛选,所以要我帮忙。好久碰Excel了,那就试试看吧。 需求是这样子的(这里做了最大化的简化):要求判断条件,男50岁以上,女40岁以上将对应列...
  • ' 依条件设置查询数组,返回包含查询字段(或全部字段)的数组,可条件组合。 ' 条件运算符包括:> = < >= <= <> , like(正则表达式) ' '附注: ' 使用此函数,需要在文件中引用正则表达式脚本 Microsoft VBScript...
  • 一、背景及需求:上一期介绍的由多个excel合成的Excel表如下所示,我们需要把有汉字的行删删除掉。 二、解决方案:同样的还是要用VBA来解决,操作前面已经介绍过了,我们直接上代码和注释(!!!在这里需要强调一下...
  • 今天和大家要说的是VBA数组的应用,上篇文章我们说的是数组的基础知识点,有明白的小伙伴可以点击链接进行知识点回顾,今天主要说说数组的几操作方法,这是我们以后会经常遇到的,也是经常会使用的方法。...
  • excel 筛选后的range数据处理

    千次阅读 2013-09-30 17:08:36
    当excel 经多次筛选后,range数据区域可能含连续的多个range,要想处理所有数据(包含隐藏的数据),可以按每行每列分 别处理。   Dim ws As Worksheet Dim rg As Range Set ws = ...
  • VBA GetOpenFilename 方法

    2021-08-02 10:54:42
    VBA中 GetOpenFilename 方法
  • 比如,一个word文档里面有多个表格,其中某个表格里面有字符串123。 我知道字符串123所在的表格是第几个,但是想要选中这个表格,怎么实现? 请各位大佬指导一下,谢谢了。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,309
精华内容 523
关键字:

vba筛选多个不包含