Solved

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

Posted on 2014-02-11
3
565 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 49

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 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