Copying Multiple Rows

Posted by Ed Acosta on December 28, 2000 6:08 PM

What I need to know: is there an opposite function for the ROWSDIFFERENCE function? I match criteria in column A based on values in a different workbook. If a match is found the spreadsheet is copied to a new spreadsheet. I use ROWSDIFFERENCE function to highlight the rows that do not match the criteria and delete them. Is there a formula that will let me highlight all the rows that meet my criteria and copy /cut & paste those rows to a different spreadsheet? The data is sorted in ascending order. I now I can use a For Next Statement to find the starting Row and Ending Row that meet the criteria. Was just wondering if there was an easier way.

Thanks

Posted by Dave on December 28, 2000 8:25 PM

Hi Ed

By far the quickest way is to use Excels Autofilters, maybe you could use something like the code below?

Sub CopyDupes()
Application.ScreenUpdating = False
With Sheets("Sheet1")
.Select
'Turn ON the filters if they aren't already
If .AutoFilterMode = False Then .Range("A1:D1").AutoFilter
'Set them to show all numbers > 5 in column A
Selection.AutoFilter Field:=1, Criteria1:=">=2"

'If no match found stop here
If .FilterMode = False Then
MsgBox "No Matching criteria"
.AutoFilterMode = False 'Turn OFF filters
Application.ScreenUpdating = True
Exit Sub
End If 'Else match has been found

'Offset by 1 row(to leave headings) and copy only the _
visible range to Sheet2 A1
.UsedRange.Offset(1, 0).SpecialCells _
(xlVisible).Copy Destination:=Sheets("Sheet2").Range("A1")
.AutoFilterMode = False 'Turn OFF filters
End With
Application.ScreenUpdating = True
End Sub

Hope this helps

Dave

OzGrid Business Applications

Posted by Ed on December 29, 2000 3:33 PM

'If no match found stop here 'Offset by 1 row(to leave headings) and copy only the _ visible range to Sheet2 A1 .UsedRange.Offset(1, 0).SpecialCells _ (xlVisible).Copy Destination:=Sheets("Sheet2").Range("A1")

Thanks for the code.