Select 1st 50 visible rows

[Dannyh1]

Hi,

I want to be able to select the 1st 50 visible rows on a filter.

I currently have the below syntax:

Set Filt = Range("B4:B15000").SpecialCells(xlCellTypeVisible)
 
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:="1"
 
Filt.Copy Destination:=Sheets("MDS").Range("B6")

Then I delete any excess rows after 50

Would appreciate help with getting this code right so I can copy 50 rows only without deleting.

Many Thanks

 

[Domenic]

Maybe something like this, where the first 50 visible rows are copied to another worksheet...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] wksSource [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] wksDest [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] Rng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FiltRng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FoundRows [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Rw [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]

    [color=darkblue]Set[/color] wksSource = Sheets("Sheet1") [color=green]'change the source worksheet accordingly[/color]
    
    [color=darkblue]Set[/color] wksDest = Sheets("MDS")
    
    wksSource.AutoFilterMode = [color=darkblue]False[/color]
    
    [color=darkblue]With[/color] wksSource
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        [color=darkblue]Set[/color] Rng = .Range("B4:O" & LastRow) [color=green]'change the range accordingly[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]With[/color] Rng
        .AutoFilter Field:=14, Criteria1:="1"
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
        [color=darkblue]Set[/color] FiltRng = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]If[/color] [color=darkblue]Not[/color] FiltRng [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Rw [color=darkblue]In[/color] FiltRng.Rows
            Cnt = Cnt + 1
            [color=darkblue]If[/color] FoundRows [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                [color=darkblue]Set[/color] FoundRows = Rw
            [color=darkblue]Else[/color]
                [color=darkblue]Set[/color] FoundRows = Union(FoundRows, Rw)
            [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]If[/color] Cnt = 50 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]For[/color]
        [color=darkblue]Next[/color] Rw
        FoundRows.Copy Sheets("MDS").Range("B6")
        wksDest.Select
   [color=darkblue]Else[/color]
        MsgBox "No records found...", vbExclamation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    wksSource.AutoFilterMode = [color=darkblue]False[/color]

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[Dannyh1]

Thanks Domenic - I would never have arrived there on my own!