Link to home
Start Free TrialLog in
Avatar of spar-kle
spar-kleFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

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

Open in new window

Avatar of spar-kle

ASKER

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

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

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

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"

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?
ASKER CERTIFIED SOLUTION
Avatar of spar-kle
spar-kle
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)

Open in new window