☰

宏跳过已过滤的行

tag-icon • tag-icon windows-10 microsoft-excel vba macros microsoft-excel-2016
宏跳过已过滤的行

我有以下宏，它执行以下操作：

  • 从 Windows 上的文件夹中获取文件名 - 仅当列表和文件夹按 A 到 Z 排序时才会执行此操作

  • 将名称与文件夹名称合并

  • 超链接连体名称

    
Dim xFSO As Object

Dim xFolder As Object

Dim xFile As Object

Dim xFiDialog As FileDialog

Dim xPath As String

Dim i As Integer

Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)

If xFiDialog.Show = -1 Then

    xPath = xFiDialog.SelectedItems(1)

End If

Set xFiDialog = Nothing

If xPath = "" Then Exit Sub

Set xFSO = CreateObject("Scripting.FileSystemObject")

Set xFolder = xFSO.GetFolder(xPath)

i = 1 'to start on row 2 and keep the heading cell

For Each xFile In xFolder.Files

    i = i + 1

    ActiveSheet.Hyperlinks.Add Cells(i, 3), xFile.Path, , , xFile.Name

    Cells(i, 5).Value = Cells(i, 4) & "\" & Cells(i, 3) '5/4/3 is the columns here

    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 5), Address:=Cells(i, 5).Formula

Next

End Sub

是否可以向宏添加一些内容以跳过电子表格中的过滤行？总共 320 行，其中 20 行被过滤掉但不是连续的（9/66/82/108/309）被过滤掉？

答案1

欢迎来到超级用户！

我认为最好的方法是将你的操作包装在for循环中，并添加一个if检查属性的语句hidden。以下是完整代码：

Sub test()

Dim xFSO As Object

Dim xFolder As Object

Dim xFile As Object

Dim xFiDialog As FileDialog

Dim xPath As String

Dim i As Integer

Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)

If xFiDialog.Show = -1 Then

    xPath = xFiDialog.SelectedItems(1)

End If

Set xFiDialog = Nothing

If xPath = "" Then Exit Sub

Set xFSO = CreateObject("Scripting.FileSystemObject")

Set xFolder = xFSO.GetFolder(xPath)

i = 1 'to start on row 2 and keep the heading cell

For Each xFile In xFolder.Files

    i = i + 1
    
    If Rows(i).Hidden <> True Then  'check if current row is hidden; if not, take action

        ActiveSheet.Hyperlinks.Add Cells(i, 3), xFile.Path, , , xFile.Name
    
        Cells(i, 5).Value = Cells(i, 4) & "\" & Cells(i, 3) '5/4/3 is the columns here
    
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 5), Address:=Cells(i, 5).Formula
        
    End If

Next

End Sub

相关内容