Retrieve multiple rows but ignore hidden rows

[xbrianx]

On 'Sheet1' I have multiple rows of data. I autofilter on Column 'A' and only display a single employee ID number. This results in only rows 15, 37, and 197 being displayed. On 'Sheet2' I want to enter a formula in cell 'A1' that pulls a specific range of the displayed (not hidden) rows in column 'A' from 'Sheet' 1. What is the formula for this?

 

[jeffmb]

Use the subtotal function.

 

[xbrianx]

Can you clarify a little more?

 

[jeffmb]

The Subtotal function will allow you to select any of a number of mathematical functions to be applied against visible cells. These functions include Sum, Average, Count, Max, and Product. The results change as you change the filter criteria.

 

[xbrianx]

Here is Sheet 1 after auto-filtering on the Pay ID (column 'A') column:

Row	Pay ID	First	Last	Action Date	Action Description
15	123456	Chuck E.	Cheese	4/25/2011	Met regarding disability
37	123456	Charles	Cheese	4/26/2012	Phone call to Mr. Cheese
197	123456	Chuck	Cheese	5/15/2012	Received initial doctor statement

<tbody>
</tbody>

So the first row that is displayed is row 15. Now on Sheet2 (my printable Action Log), I have the following:

Row
1	First:	Amanda
2	Last:	Huginkiss
3	Payroll ID:	999123
4	Action Date	Action Description
5
6
7
8
9
10

<tbody>
</tbody>

So on Sheet2 in cell C1, C2, and C3... I want the formula to pull the first record on of Sheet1 in column B. On sheet 1, with the rows unfiltered, the first record is for Amanda Huginkiss. However, I've fileted the results to only display Chuck E. Cheese which the first result is row 15. So in cell A5, A6, and A7 (and B5, B6, and B7 accordingly), I want the action log to pull only Chuck E. Cheese's records. Is this possible?

 

[jeffmb]

Try the following vba code:

Sub Filter_Copy()
Dim lastrow As Integer
    lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
    Sheets("Sheet1").Range("D2:E" & lastrow).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy Destination:=Sheets("Sheet2").Range("A5")
    Application.CutCopyMode = False
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

 

[jeffmb]

If you do not want to run a macro every time you change the target you can try copying the following function into a module:

Function Nth_Occurrence(range_look As Range, find_it As String, _
        occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
    ' This function finds the nth occurrence of the find_it string in the range_look
    ' range, and will return the row and column offset
    '
    ' Example: Nth_Occurrence(Sheet1!$A$2:$A$100,"Smith",2,0,3))
    '   will find the second occcurrence of "Smith" in the range A2:A100 and will return
    '   the value in the cell offset by 0 rows and 3 columns
    
    Set rFound = range_look.Cells(1, 1)
    For lCount = 1 To occurrence
        Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
    Next lCount
    Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function

Then put the following formulas into cells A5 and B5 on Sheet2:
A5:
=IF(ROW() - 4 > COUNTIF(Sheet1!A:A,B$3),"",Nth_Occurrence(Sheet1!$A$2:$A$100,B$3,ROW() - 4,0,3))[/CODE]
B5:
=IF(ROW() - 4 > COUNTIF(Sheet1!A:A,B$3),"",Nth_Occurrence(Sheet1!$A$2:$A$100,B$3,ROW() - 4,0,4))[/CODE]

Then autofill down as far as you think is needed. Your table on sheet1 does not need to be filtered for this.

 

[xbrianx]

I think this second codes does the trick. Thanks for your help.