How can I use the "special" cells visible after AutoFilter has been applied to extract data in VBA?

You don't need to, Excel will only copy visible cells.

Attach your workbook and I'll check it if you want. Your posted code does not use AutoFilter

Try below and add .Copy after Special Cells

Sub FilterSpecial()
Dim Ws As Worksheet
Dim LRow As Long
Dim FilterRng As Range
Dim FilterVal As String
Set Ws = Worksheets("Sheet1")
LRow = Ws.Range("D" & Rows.Count).End(xlUp).Row
Set FilterRng = Ws.Range("A1:D" & LRow)
FilterVal = "125"
Application.ScreenUpdating = False
With FilterRng.Parent
    .AutoFilterMode = False
End With
With FilterRng
    .AutoFilter Field:=1, Criteria1:=FilterVal
    .SpecialCells(xlCellTypeVisible).Copy  'You may use .Copy or .EntireRow.Delete
    'Paste where ever you want
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Select all Open in new window

Thanks for the replies.

I wanted to use the data to compile a csv file with vba into a text file.
This means that I only want selected data from the applied filter.
I don't want to copy all of the data.

I just attached some code to give a sense of what I wanted to do.
I actually want to give some values to variables from the resultant autofilter.

The following code probably shows better what I want to achieve:

Dim LastRowA As Long, x as integer, String1 as String

With Sheets("Sheet1")
LastRowA = .Cells(.Rows.Count, "D").End(xlUp).Row
End With

For x = 1 To LastRowA
String1 = Range("A" & x).value
'insert code here
Next x

Select all Open in new window

As I said if data is filtered Excel will only copy the visible cells, you do not need to use SpecialCells

Auto Filter is much faster than Loop, try below, assuming you need to copy from Sheet1 to Sheet2

Sub FilterSpecial()
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim LRow As Long, DRow As Long
Dim FilterRng As Range
Dim FilterVal As String
Set Ws1 = Worksheets("Sheet1")
Set Ws2 = Worksheets("Sheet2")
LRow = Ws1.Range("D" & Rows.Count).End(xlUp).Row
DRow = Ws2.Range("A" & Rows.Count).End(xlUp).Row
Set FilterRng = Ws1.Range("A1:D" & LRow)
FilterVal = "125"
Application.ScreenUpdating = False
With FilterRng.Parent
    .AutoFilterMode = False
End With
With FilterRng
    .AutoFilter Field:=1, Criteria1:=FilterVal
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy 'You may use .Copy or .EntireRow.Delete
    Ws2.Range("A" & DRow + 1).PasteSpecial (xlPasteValues) 'Paste where ever you want
End With
Application.CutCopyMode = False
Ws1.AutoFilterMode = False
Ws2.Activate
Ws2.Range("A1").Select
Application.ScreenUpdating = True
End Sub

Select all Open in new window

Thank you for your attention guys, appreciate that.

Ok, I know I'm not making myself very clear, sorry!
I don't want to copy all of the data from the resultant autofilter, I would just like to give a value to a number variables from the result.
The sheet will be several thousand rows long, so I wanted to reduce the amount of data to be processed first using an autofilter.
(So that any looping would be reduced)
This will also allow the user to view the results before processing the data.
I've attached a sample file that I've just rigged up that hopefully shows the process.

The "CREATE" button has no code behind it yet
Example.xlsm

This will filter the sheet based on data stating in A!. It will filter on  column A for 125 and copy to sheet without the header rows. Note it does not require SpecialCells.

Option Explicit

Sub CopyVisFilter()
    Dim oWS As Worksheet
    Dim rRng As Range
    '
    Set oWS = Sheet1
    '
    With oWS

        If Not .AutoFilterMode Then .Range("A1").AutoFilter
        .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="125"
        Set rRng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1)
        rRng.Copy Sheet2.Cells(1, 1)
    End With
    ''/// clear clipboard
    Application.CutCopyMode = False
End Sub

Select all Open in new window

What is wrong with the Form in your attached sheet?

AutoFilter does not Loop.

What do you mean by this?

I don't want to copy all of the data from the resultant autofilter, I would just like to give a value to a number variables from the result.

here's a very simple example based on my code, note you can add more rows and columns
AutoFilter.xlsm

Okay, nothing is wrong with the Userform.
I want to put some code behind the CREATE button that gives values to about 5 variables based on the resultant filter.

I normally build the CSV file in VBA by adding a line to a txt file, then cycling through the data in a worksheet row by row to complete the file before saving as a csv.

Is this what you want? It removes the headers but that can be changed
Example.xlsm

Just read the last post, this code will save as csv

Sheet3.copy
ActiveWorkbook.SaveAs "Name here" & ".csv"

Select all Open in new window

Thanks Roy

...I can of course copy the autofilter results to a new sheet, then extract/manipulate the data before creating a csv.
However, I wanted to compile the csv from the results without pasting.

Heres a copy of the csv file I need to create, which I usually create from a much smaller worksheet with only 40 rows.
Sample.csv

This does not remove the headers and saves as CSV
Example.xlsm

Thank you Roy.
However that is not correct,.

My original question was "How can I use the special cells, visible after the Auto Filter?"
I can create a csv file from a worksheet without a problem.
(Actually, just changing a worksheet extension doesn't achieve this... you need to change the format of the file save)
Anyway, I know how to do that.

Ultimately I need to produce a csv of the following format.
(I hadn't gone into this much detail because I was just looking for a technique of giving variables a value, not the whole solution.)
Engineer_3,SATELLITE,,,,,,,,,,123423/1 123464/1,,,,,,,UPS,,
980425,,11,,
042588,,15,,
T,Stock sent to replenish satellite stock,,,
CARRIAGE,,,0,

Your method, once corrected gives this format:
Date,Engineer,Type,Customer,Quote,Chit,Line No.,Qty,Code,Description,Stock,Available,Balance,Sent,Advice,Returned
18-09-04 16:15,Engineer_1,RET,Customer_1,1234.1,123485/1,1,1,000009,Part1,5,1,0,,,
18-09-04 16:16,Engineer_1,RET,Customer_4,1888.4,123423/4,16,16,059810,Part8,64,16,0,,,

I can build the csv file from a worksheet, I was just wanting to get the best method by reducing the range first then assigning some variables a value from the result

Third time, you do not need to use special cells to copy visible filtered rows. My code demonstrates that point, with or without headers whichever you want.

The code does save as csv, but that was not part of the question, I was just trying to help.

Before clicking Create Button, make sure you have correct folder path in J1.
Try in attached...
sparkle_SaveAsCSV.xlsm

Thank you Roy & Shums
I'm sorry if my requests are irritating you.
I really appreciate that your trying to help, but I think we are talking at cross purposes.
I have used the copy and paste method of using data from autofilter on many occasions.

However as stated I didn't want to "copy" and "paste" the data if that was not necessary.
I wanted to use the Autofilter results without copying them to another location.

Basically I was looking for a method that was quicker than looping through each row of the sheet before compiling the csv.
I thought there may be a more elegant way of handling the data

So here's the real question:
Are you both saying that it's not possible to extract data from the autofilter results without copying the data elsewhere first?

Thank you both for the time you spent trying to assist me. i really appreciated that.

How can I use the "special" cells visible after AutoFilter has been applied to extract data in VBA?

That was the original question, I stated 3 times that you do not need to use SpecialCells to copy AutoFiltered data. My eamples proved that point, irrespective of the csv requirement which was not in the original question, nor indeed your use of areas. This code in your answer was lifted from my code except that you have added SpecialCells, which isn't necessary

With Sheet1
Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

Select all Open in new window