finding all values in a sheet less than stated cell and pasting

[robjowens]

Hi Peeps,

How would I go about amending the below to paste all values below the cell D21 in the Beacon Data sheet.

Cheers,
rob

Sub CandidateList()
With Worksheets("Results").Range("TotalNPV")
Set c = .Find(Worksheets("Beacon Data").Range("D21").Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Copy Destination:=Worksheets("Candidate List").Range("a" & Worksheets("Candidate List").Range("a64000").End(xlUp).Row + 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Sheets("Candidate List").Select
End Sub

 

[Jerry Sullivan]

Hi Rob,

Your current code is doing a .Find and .FindNext for a single value.
If you want to find all values that meet a criterion -in this case "Less than D21",
then you would be better off using something other than .Find and .FindNext.

I'd suggest you take the approach of using Advanced Filter to copy only the rows that meet your criterion.

You can find example code on this Board.

Just ask if you need some help getting that approach to work with your specifics.

 

[robjowens]

thanks, i worked this out after about 40mins of stressing and recorded a fresh macro using the advanced filter. will post for reference/search purposes when i'm next in the office.