Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 795
  • Last Modified:

Copy ten first rows in range omitting hidden rows (VBA)

I have a sheet with filtered and sorted data and I want to copy the first ten visible rows and paste into another range.

How can I omit the rows hidden by filter and still get exactly ten?
0
Mamarazzi
Asked:
Mamarazzi
1 Solution
 
Rgonzo1971Commented:
HI,

You could use this method
Sub Macro()
Limit = 10
Idx = 1
Set myRange = Range("A1:C25").SpecialCells(xlVisible)
For Each myArea In myRange.Areas
    For Each rw In myArea.Rows
        If Idx <= Limit Then
            strFltrdRng = strFltrdRng & rw.Address & ","
            Idx = Idx + 1
        End If
    Next
Next
strFltrdRng = Left(strFltrdRng, Len(strFltrdRng) - 1)
Set myFltrdRange = Range(strFltrdRng)
myFltrdRange.Copy

End Sub

Open in new window

EDIT Renaming vars

Regards
0
 
MamarazziAuthor Commented:
Perfect, thanks a lot!
0
 
Suresh ArunthathiyarCommented:
i want to copy first 50 visible rows and paste into another range, i tried to change the limit as 50. However im getting some error 'Method range of Object_Global failed'.

Please assist me with the same
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now