AutoFilter help!

[golfnut019]

After autofiltering the data that I need, I need to copy that data onto another worksheet.

How do I specify the range? There are some rows that are not showing that I don't need and the rows that i DO NEED start at 100 and this will always change daily. I need a formula/method to select the first row and on after being autofiltered.



TIA

 

[erik.van.geit]

Hi, golfnut019
Welcome to the Board !

this is not really clear ...
seems like you want some code

what are the criteria for this "changing" row ? you need some definition of the "first" row
or is it the last row which is changing ?

just copying the range and pasting will only paste the visible rows, if made invisible by autofilter

an example would help

kind regards,
Erik

 

[golfnut019]

For example :

The data table I have is ordered by partner ID, partner name, spend $, and date. Now, I want the data for only 11/16/2005. When I autofilter it, 11/1-11/15 is taken out and now I am left w/ the rows of data (there are multiple rows of 11/16 as well as other dates) that is associated with the date/transactions relating to the date 11/16/2005.

How do I specify the range of the autofiltered data? The autofilter headers start at row 4 but after autofiltering, depending on the date, for 11/16 the row after the header starts at 122. How I select all the data for 11/16?


TIA

 

[erik.van.geit]

what do you mean by "select"
I presume you want code

how is the last row defined ?
OR
in what column can we find the last row ?
try something like this
it will copy rows 4 to lastrow to sheet2 cellA1

Sub test()
LR = Cells.Find("*", [A1], xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
FR = 4
Rows(FR & ":" & LR).Copy Sheets(2).Cells(1, 1)

End Sub

does this get you in the right direction ?

best regards,
Erik

 

[golfnut019]

The only way i would know how to define the last row is that there is no data after the last row?

So, I'm guessing using the .End(xlUp) function?

But I don't know where to start in terms of the first row value to use since the autofilter will always have the first row after the autofilter being executed, being different. (i.e. after setting the autofilter, the first row could be from row 10 today, could be row 122 tomorrow and so on)

 

[Greg Truby]

Not a lot of time here, but in general:

1. If you are copying the header too then simply use [A1].CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Destination:=destinationrange
2. If you are appending, i.e. not copying the header then set a range variable to the cells that are being filtered but does not include the header; call it rngDataCells for argument's sake. Then rngDataCells.SpecialCells(xlCellTypeVisible).Copy Destination:=destinationrange will do the trick, but you need to put an On Error Resume Next beforehand because if there are not cells visible after applying the autofilter, this will error out.

HTH

 

[erik.van.geit]

didi you see my last reply ?
when you copy the data you don't need to know the first visible row, since hidden rows will not be pasted as i said earlier

the LR (last row) formula I used could be replaced by something like
Cells(Rows.Count, 1).End(xlUp).Row
(looking in first column)
but you didn't tell in which column to look for the last row, you see ?

if you really want to know the first row
FR = Rows(FR & ":" & LR).SpecialCells(xlCellTypeVisible)(1).Row

best regards,
Erik

 

[erik.van.geit]

Hi, Greg,

in XP you don't need to code
.SpecialCells(xlCellTypeVisible).Copy
when copying a filtered range
the invisible rows won't be copied anyway
at least that's the way it works in XP
do you have other experience ?

kind regards,
Erik

 

[Greg Truby]

I picked up that habit years ago, Erik. Back when using XL95 as I recall. But you are correct. I just tested this several ways and range commands are indeed only impacting visible (non-filtered) cells on an autofiltered range. So I guess my knowledge is obsolete! Thanks for the tip.

Sub quickaftest()
    Dim r As Range, d As Range
    Set r = Sheet1.Range("a1").CurrentRegion
    Set r = r.Offset(1).Resize(r.Rows.Count - 1)
    Set d = Sheet2.Range("a1")
    r.Copy d
    r.Interior.ColorIndex = 42
    Sheet1.AutoFilter
End Sub

 

[erik.van.geit]

Greg,

OK, do you know if we still would need it in XL97 ?
(never used 97)