Solved: easiest fastest way to copy entire rows

[philfer]

Hi,

I have a worksheet in a workbook with lots of ledger codes. I want to loop down column A and when it finds a particular code saya 2010 I want to copy the entire row into another worksheet so that the new worksheet only has the details for this ledger code 2010.

I am using a loop (For i = 1 to lastrow) and then doing

Activesheet.Cells(i,1).EntireRow Copy Sheet("Sheet2").Range(j,1)

(j is obtained by finding the lastrow on the new sheet then adding 1 each time 2010 is found)

but it is SLOOOOOOOW.

It takes forever (well about a minute but in macro time it seems like forever)

Is there a quick way of doing this

[Bob Phillips]

Use a filter to selec t the matching records and then copy the visible rows.

[philfer]

Do you have an example of code which could do this

[Bob Phillips]

Public Sub DeleteByFilter()
Const TEST_COLUMN As Long = 1 '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim rng As Range
Dim sh As Worksheet

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set sh = ActiveSheet

With sh
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

.Columns(TEST_COLUMN).AutoFilter Filed:=1, Criteria1:=2010
On Error Resume Next
Set rng = .Cells(2, TEST_COLUMN).Resize(LastRow - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rng Is Nothing Then rng.Delete

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

Set sh = Nothing

End Sub