Solved

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

Posted on 2014-02-11
3
597 Views
Last Modified: 2016-10-24
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
Comment
Question by:Mamarazzi
3 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 300 total points
ID: 39850164
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
 

Author Comment

by:Mamarazzi
ID: 39852727
Perfect, thanks a lot!
0
 

Expert Comment

by:Suresh Arunthathiyar
ID: 41858086
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

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question