VBA - Copying a subset of rows within a Range of filtered data

[daveclee]

Hi,

I know that the answer to this is simple, but I can't find the answer!!
I have the following code:

Dim rng As Range

Set rng = ActiveSheet.AutoFilter.Range
rng.Copy _
Destination:=Worksheets("Sheet2").Range("A1")

This copies ALL of the filtered Rows into a separate worksheet.
What I want to do is:
1) COPY THE 1st 100 rows of the range.
2) Copy the 100 rows as a separate worksheet in a another Workbook that is already opened in Excel

Any answers?

Thanks!

Dave

Any answers?

 

[jbeaucaire]

Copy the whole range to the target destination, then in the destination file delete everything in row 101 (102?) down.

 

[daveclee]

Not a bad idea... but not the most 'elegant'

 

[jbeaucaire]

Since the only other thing I can think of is looping through 100s and 100s of cells making a list of the ones that are hidden and the ones that are not, I think one copy command and one delete command is much more elegant.

 

[AlphaFrog]

    Dim rng As Range, r As Long, rowcount As Integer, lastrow As Long
    
    lastrow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
    For r = 1 To lastrow
        If Rows(r).Hidden = False Then
            If rng Is Nothing Then
                Set rng = Rows(r)
                rowcount = 1
            Else
                Set rng = Union(rng, Rows(r))
                rowcount = rowcount + 1
            End If
            If rowcount = 100 Then Exit For
        End If
    Next r
    rng.Copy Destination:=Worksheets("Sheet2").Range("A1")

 

[jbeaucaire]

See what I mean about all the looping? Just as easy:

Sub First100()
Dim RNG As Range:  Set RNG = ActiveSheet.AutoFilter.Range

Application.ScreenUpdating = False
    RNG.Copy Sheets("Sheet2").Range("A1")
    Sheets("Sheet2").Range("A101:A" & Rows.Count).Clear
Application.ScreenUpdating = True
End Sub

 

[daveclee]

jbeaucaire and AlphaFrog,

Thank you both so much for your help. Both sets of code work well... this is a classic case of 'Clean Code' vs. 'Fast Code'. Since I am working will an Excel File that is over 120MB to run this Macro on, the Fast Code is one to go with in this situation.

jbeaucaire - I gotta check out more of your tools... they look way cool!