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

I use a Userform to setup an AutoFilter
How can I use the special cells, visible after the Auto Filter?
Obviously the attached code will refer to all data in the worksheet.
Can I adapt it so that it just focuses on special visible cells?

Dim LastRowA As Long, x as integer

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

For x = 1 To LastRowA
If Range("A" & x).value = 125 Then
'insert code here
End If

Next x

Open in new window

spar-kleOperations DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
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
0
ShumsDistinguished Expert - 2017Commented:
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

Open in new window

0
spar-kleOperations DirectorAuthor Commented:
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

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roy CoxGroup Finance ManagerCommented:
As I said if data is filtered Excel will only copy the visible cells, you do not need to use SpecialCells
0
ShumsDistinguished Expert - 2017Commented:
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

Open in new window

0
spar-kleOperations DirectorAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
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

Open in new window

0
ShumsDistinguished Expert - 2017Commented:
What is wrong with the Form in your attached sheet?
0
Roy CoxGroup Finance ManagerCommented:
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
0
spar-kleOperations DirectorAuthor Commented:
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.
0
Roy CoxGroup Finance ManagerCommented:
Is this what you want? It removes the headers but that can be changed
Example.xlsm
0
Roy CoxGroup Finance ManagerCommented:
Just read the last post, this code will save as csv

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

Open in new window

0
spar-kleOperations DirectorAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
This does not remove the headers and saves as CSV
Example.xlsm
0
spar-kleOperations DirectorAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
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.
0
ShumsDistinguished Expert - 2017Commented:
Before clicking Create Button, make sure you have correct folder path in J1.
Try in attached...
sparkle_SaveAsCSV.xlsm
0
spar-kleOperations DirectorAuthor Commented:
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?
0
spar-kleOperations DirectorAuthor Commented:
ok ...sorry if I upset you guys but I was trying to explain my requirement, and I'm sure if you reread my posts that would be more clear to you.

Anyhow I found a solution to my question which I've altered to show what I was after.
The attached code will focus on the filtered data and enables the assigning of variables as required.
I've just used the message box which helps to see where to put thevariables.

Using this code means that you don't have to copy and paste the results from AutoFilter before using it.

This method works if data has already been filtered

If this may not be the case you can use
If Not .AutoFilterMode Then 'Data filter row is not present
'or alternatively/additionally 'Data filter row is present but no filter applied
If Not .FilterMode Then '

Sub ExtractDataFromAutoFilter()
Dim rng As Range, rngA As Range, rngC As Range, Rows_1 As Long, Row_2 As Long
     
With Sheet1
Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

lRows = 0
For Each rngA In rng.Areas
For Each rngC In rngA
Rows_1 = Rows_1 + 1

Row_2 = rngC.Row
MsgBox Range("C" & Row_2).Value & " is on row " & Row_2
'or Variable = Range("C" & lRow2).Value 'or specify any variable and refer to any other cell within the range

Next rngC
Next rngA
        
End With
     
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spar-kleOperations DirectorAuthor Commented:
Thank you both for the time you spent trying to assist me. i really appreciated that.
0
Roy CoxGroup Finance ManagerCommented:
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)

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.