VBA – Save text from filtered table in Excel in separate file

I the current article I will simply present how to save the results from a filtered table in MS Excel in a separate text file. To do this you simply need some table as the following one, where you can have some values in Excel cells. To simplify the current article, I have created only two columns – Name and Town. I will use the second one to filter the values, equal to Sofia.

Once they are filtered, I have only the values on the lines of “Peter”, “Keeper”, “Nothing” and “To”, which I will try to export as a text file, located on the same folder, as the current file.

After running the macro below, this is the result:

How do we achieve this result? This is what we do in the VBA code:

1. We define the location of the current file (sPath).
2. We copy the used range of the sheet.
3. We define the last row of the used range.
4. Then we open the sPath for input and with two embedded loops we go around the table and for each row we generate a line like the following “Peter”;”Sofia”.
5. We close the file and we are ready.

The code is the one below:

Option Explicit

Sub SaveFilteredResults()

    Dim iFirstCounter       As Integer
    Dim iSecondCounter      As Integer
    Dim sText               As String
    Dim sPath               As String
    Dim iLast               As Integer
    
    sPath = ThisWorkbook.Path & "\Output.txt"
    Sheets("Sheet1").Activate
    ActiveSheet.UsedRange.Copy
    Sheets.Add
    Range("A1").Select
    ActiveSheet.Paste
    iLast = Range("A65536").End(xlUp).Row
    
    Open sPath For Output As #1
    
    For iFirstCounter = 1 To iLast
        For iSecondCounter = 1 To ActiveSheet.UsedRange.Columns.Count
            sText = sText & ";" & Cells(iFirstCounter, iSecondCounter).Value
        Next iSecondCounter
        
        sText = Mid(sText, 2, Len(sText) - 1)
        Print #1, sText
        sText = ""
    Next iFirstCounter
    
    Close #1

End Sub

Enjoy it! 🙂