Retrieve multiple rows but ignore hidden rows

xbrianx

New Member
Joined
Dec 2, 2010
Messages
22
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?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jeffmb

Board Regular
Joined
Jul 18, 2008
Messages
184
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

New Member
Joined
Dec 2, 2010
Messages
22

ADVERTISEMENT

Here is Sheet 1 after auto-filtering on the Pay ID (column 'A') column:
RowPay IDFirstLastAction DateAction Description
15123456Chuck E.Cheese4/25/2011Met regarding disability
37123456CharlesCheese4/26/2012Phone call to Mr. Cheese
197123456ChuckCheese5/15/2012Received 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
1First:Amanda
2Last:Huginkiss
3Payroll ID:999123
4Action DateAction 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

Board Regular
Joined
Jul 18, 2008
Messages
184
Try the following vba code:

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
 
Last edited:

jeffmb

Board Regular
Joined
Jul 18, 2008
Messages
184
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:
Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,609
Members
414,080
Latest member
penguin23

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