macro and row after header

[SharonMee]

Hello all:

I need your help with this macro. In column R, I have filtered the data for "NO", then I want to copy all the rows and paste then into another sheet. I am having a problem with the copy part of the macro. I want to copy anything apart from the first row of my data since that is the header, but I don't want to specify the row #s as my macro recorder did since the data will change from time to time. I just want to copy from the row after the header to the last row.

Sheets5.Select
Range("R1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=18, Criteria1:="No"
Rows("236:1160").Select
Selection.Copy

I know with the code below, I can get the last row:

Range("b1").Select
Selection.End(xlDown).Select
ro$ = ActiveCell.Row

Thanks for helping.

SharonMee

 

[PHV]

Take a look at .SpecialCells(xlCellTypeVisible)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Molby]

Sharon try the below code

ActiveCell.SpecialCells(xlLastCell).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Cells(2, 1)).Select
Selection.Copy

 

[Loomah]

Hi Sharon
Assuming your data starts in A1 and is contiguous the following will copy all your visible data excluding the first row.

With Worksheets(1).Range("A1").CurrentRegion
    .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Worksheets(2).Range("H2")'or whatever!
End With

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244