Filter and select the upmost rows

[Ranjit kurian]

Hi

I have three columns (Item, Total, Name), the below code goes to item column
then put autofilter, after that selects Business from the item column , item
column contains Business, Sales, Scrapes etc..., till here its working fine
after this i need the macro to select and copy the first five rows only.

Actually when you put autofilter to select Business, few columns like sales
, scrapes etc.. column will be hidden , which i do't need when iam selected
Business, and the range also differ always..

Sub test()
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Business"
Range("A1:C1").Select

' copy only five rows

End Sub

 

[Gary''s Student]

This small macro will copy the header row and the first 5 visible rows. For
test purposes I pasted in A100:

Sub copyfilter()
k = 0
Set r = Nothing
For i = 1 To Rows.Count
If Cells(i, 1).EntireRow.Hidden = True Then
Else
If r Is Nothing Then
Set r = Cells(i, 1)
Else
Set r = Union(r, Cells(i, 1))
End If
k = k + 1
If k = 6 Then Exit For
End If
Next
r.EntireRow.Copy Range("A100")
End Sub

 

[Ranjit kurian]

Hi Gary

Below is my table, when i filter for business it should copy only the five
rows.

Item Total Name Name

business 1 a
sales 2 b
scraps 3 c
business 1 a
sales 2 b
scraps 3 c
business 1 a
sales 2 b
scraps 3 c
business 1 a
sales 2 b
scraps 3 c
business 1 a
sales 2 b
scraps 3 c
business 1 a
sales 2 b
scraps 3 c
business 1 a
sales 2 b
scraps 3 c
business 1 a
sales 2 b
scraps 3 c
business 1 a
sales 2 b
scraps 3 c