Macro to skip filtered rows

drish

New Member
Joined
Dec 15, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi,

Spreadsheet as follows:

Column A - file name
Column B - email address
Colum C - file name (this is pulled from the folder when macro is run)
Column D - folder path
Column E - hyperlink created (from the macro)

Macro below:

Sub Combined()

'Gets file names

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
Next

'Adds the name to folder

Dim x As Integer

For x = 1 To 6 'Number will change depending on how many rows you have to fill
Cells(x, 5).Value = Cells(x, 4) & "\" & Cells(x, 3) '5/4/3 is the columns here
Next x

'Converts each text hyperlink selected into a working hyperlink

Dim xCell As Range

For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell

End Sub

I am trying to see if it is possible to add something to the macro that will skip filtered rows.

The macro draws the files from the folder and attaches it to the spreadsheet but should a file not be in the folder for one month and highlighted in the spreadsheet, it does not skip that and allocates the wrong file to the information in Column A.

Any assistance is appreciated.

Thanks!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Watch MrExcel Video

Forum statistics

Threads
1,130,009
Messages
5,639,530
Members
417,094
Latest member
IsimiKehinde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top