Loop

[watkins6878]

Hi

I am trying to loop through a sheet looking in column "D" for a word and when it find the word i want it to copy the entire row to another sheet but data is being addedall the time . how can i get it to loop until it gets to the last row with data in it. I would be greatful for any help

Thanks

alan

 

[Legacy 68403]

try the following code for looping until it gets to the last row with data in it.

Sub test()
Range("D1").select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[Boller]

If the word you are looking for occupies the cells on its own, instead of looping, AutoFilter could be used :-

With Range([D1], [D65536].End(xlUp))
    .AutoFilter 1, "the word"
    .Offset(1).Resize(.Rows.Count - 1) _
            .SpecialCells(xlVisible).EntireRow.Copy _
            Sheets("Sheet2").[D65536].End(xlUp)(2, -2)
    .AutoFilter
End With

The code assumes you have a header row.