Optimize Excel VBA to Filter and Export Data to a Text File
Question details
The user needs to speed up a VBA workflow that filters a large Excel dataset, copies the results to another sheet, and exports a column to a text file.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A macro processes a large dataset and must prepare filtered records for a later text-file export.
- Observed behavior
- Processing rows individually can be slow, and the existing workflow does not yet complete the filtered-data copy and text-file export.
Identify the source filter range, the output sheet, and the column to export. Confirm whether the first 14 rows means worksheet rows or data rows; the example below does not delete either.
Copy filtered Excel rows and export a column with VBA
Use this Windows Excel example after applying a worksheet filter to copy visible results to Sheet3 and export one output column.
The macro temporarily disables screen updates, automatic calculation, events, and status-bar display, then restores their previous settings even if an error occurs. It copies visible cells in one operation and reads the export column into an array before writing the file.
This example expects a worksheet AutoFilter, not an Excel table filter. It clears all cell contents on Sheet3 and overwrites filtered-export.txt on the Windows desktop. Show all source columns before running it so the output column positions match the source.
In the source worksheet, select the data range, choose Data > Filter, and use the header arrows to apply the required criteria. Leave the source worksheet active.
Press Alt+F11, choose Insert > Module, and paste the following code. Change EXPORT_COL to the output-sheet column number you need; for example, 2 exports column B. Sub ExportFilteredData() Const EXPORT_COL As Long = 2 Dim src As Worksheet, dst As Worksheet, filtered As Range Dim lastCell As Range, values As Variant, desktopPath As String Dim lastRow As Long, i As Long, fileNum As Integer Dim oldScreen As Boolean, oldEvents As Boolean, oldStatus As Boolean Dim oldCalc As XlCalculation, fileOpen As Boolean, errorText As String Set src = ActiveSheet oldScreen = Application.ScreenUpdating oldEvents = Application.EnableEvents oldStatus = Application.DisplayStatusBar oldCalc = Application.Calculation On Error GoTo CleanUp Set dst = ThisWorkbook.Worksheets("Sheet3") If src Is dst Then Err.Raise vbObjectError + 1, , "Select a source sheet other than Sheet3." If Not src.AutoFilterMode Then Err.Raise vbObjectError + 2, , "Apply a worksheet filter first." Set filtered = src.AutoFilter.Range If EXPORT_COL < 1 Or EXPORT_COL > filtered.Columns.Count Then _ Err.Raise vbObjectError + 3, , "EXPORT_COL is outside the filter range." Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayStatusBar = False Application.Calculation = xlCalculationManual dst.Cells.ClearContents filtered.SpecialCells(xlCellTypeVisible).Copy Destination:=dst.Range("A1") Set lastCell = dst.Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If lastCell Is Nothing Then Err.Raise vbObjectError + 4, , "No cells were copied." lastRow = lastCell.Row If lastRow < 2 Then Err.Raise vbObjectError + 5, , "No data rows are visible." desktopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") fileNum = FreeFile Open desktopPath & "\filtered-export.txt" For Output As #fileNum fileOpen = True If lastRow = 2 Then Print #fileNum, dst.Cells(2, EXPORT_COL).Value2 Else values = dst.Range(dst.Cells(2, EXPORT_COL), dst.Cells(lastRow, EXPORT_COL)).Value2 For i = 1 To UBound(values, 1) Print #fileNum, values(i, 1) Next i End If CleanUp: errorText = Err.Description If fileOpen Then Close #fileNum Application.Calculation = oldCalc Application.DisplayStatusBar = oldStatus Application.EnableEvents = oldEvents Application.ScreenUpdating = oldScreen If Len(errorText) > 0 Then MsgBox errorText, vbExclamation Else MsgBox "Export saved to the desktop." End If End Sub
Return to the source sheet, press Alt+F8, select ExportFilteredData, and click Run. Check Sheet3 and open filtered-export.txt on the desktop to verify the rows and values.

Work with filtered spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface for opening spreadsheets and working with filtered data. Its Microsoft Office format support helps you move existing files into WPS, although it does not fix Excel VBA issues.

Frequently Asked Questions
Why does the macro copy only the header?
The filter may have hidden every data row. Check the filter arrows on the source sheet and adjust the criteria before running the macro again.
How do I export a different column?
In the VBA editor, change the EXPORT_COL constant to the column's position on Sheet3. For example, use 1 for column A or 3 for column C.
Does this macro remove the first 14 rows?
No. Confirm whether the requirement refers to worksheet rows or data rows before adding a deletion or exclusion step.
Where does the text file go?
The example writes filtered-export.txt to the Windows desktop and overwrites an existing file with that name. Change the path in the Open statement if you need another location.
Will the example work with an Excel table filter?
Not as written. It uses the worksheet's AutoFilter range; a table-based workflow needs to reference the table's ListObject and its filtered range instead.




