Excel filter range
 

Hi there. I'm hoping someone can help me with this problem. I can'
figure it out.

I have 3 spreadsheets, sheet A, sheet B and sheet C. I have used
filter on A, and now some rows are visible and some aren't. I woul
like to be able to copy the rows corresponding to the ones showing i
sheet A, from B to C. That is, if only rows 3 and 5 are showing in A
i would like to copy rows 3 and 5 of B to C. Or, i could make sheet
show the same rows as A, so that only rows 3 and 5 are showing. Bu
sheet B does not have the same information as A, so i can't just run
filter on it.

I have found ways to do the copying, but they involve straight copyin
and pasting of the rows, and each sheet has thousands or rows, so thi
takes too long, and made Excel crash once because of memor
constraints.

I'm not sure if i'm explaining my problem clearly. Please help!

thanks

--
Message posted from http://www.ExcelForum.com

Excel filter range
 

----- shamoling wrote: ----

Hi there. I'm hoping someone can help me with this problem. I can'
figure it out

I have 3 spreadsheets, sheet A, sheet B and sheet C. I have used
filter on A, and now some rows are visible and some aren't. I woul
like to be able to copy the rows corresponding to the ones showing i
sheet A, from B to C. That is, if only rows 3 and 5 are showing in A
i would like to copy rows 3 and 5 of B to C. Or, i could make sheet
show the same rows as A, so that only rows 3 and 5 are showing. Bu
sheet B does not have the same information as A, so i can't just run
filter on it

I have found ways to do the copying, but they involve straight copyin
and pasting of the rows, and each sheet has thousands or rows, so thi
takes too long, and made Excel crash once because of memor
constraints

I'm not sure if i'm explaining my problem clearly. Please help

thanks


--
Message posted from http://www.ExcelForum.com

Hi, shamoling. I hope this works - I have not tested it. I have a code from Tom Ogilvy that increments down a visible rnage. I call that in other macros where I have to do things with a filtered list. A Google search through the newsgroups came up with some other code to set a range on another sheet and copy a row over. Here's what I came up with. I hope it works for you

E

Sub Copy_Filtered_Row

Dim thisRow As Long, nextRow As Lon
Dim rngC As Rang
Set rngC = Sheets("Sheet C").Range("A" & Rows.Count).End(xlUp

Sheets(€œSheet A€�).Range(€œA1€�).Selec

Do

€˜Get row numbe
thisRow = ActiveCell.Ro

€˜ Increment to next visible ro
Increment

€˜Get row numbe
nextRow = ActiveCell.Ro

€˜ The code to iterate through visible cells will stay on the last visible cell
€˜ The If detects whether the Selection has moved to a different ro
€˜ since the last iteration. Theres probably a much better way
€˜ but I dont know it yet
If thisRow = nextRow The
Exit D
End I

€˜ Copy B to
Sheets(€œSheet B€�).Range(nextRow, 1).EntireRow.Copy Destination:=rng

Loo

End Su

Sub Increment1(

Dim rng As Range, rng1 As Rang
Dim icol As Lon
icol = ActiveCell.Colum

Set rng = ActiveSheet.AutoFilter.Rang
Set rng = Intersect(rng, Columns(icol)
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count)

On Error Resume Nex
Set rng1 = rng.SpecialCells(xlVisible
On Error GoTo

If Not rng1 Is Nothing The
rng1(1).Selec
End I

End Su