AutoFilter help!

golfnut019

Board Regular
Joined
Nov 11, 2005
Messages
58
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Nov 11, 2005
Messages
58
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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

Board Regular
Joined
Nov 11, 2005
Messages
58

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,351
Messages
5,595,646
Members
414,005
Latest member
BradWarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top