How would I copy the numbers greater than 10 from a list.

[kbj0109]

Does anyone know how to create a VBA code that copies all the numbers greater than 10 from a list, and pastes the numbers to the cell B3?

The list starts from A3 to xlDown in A column, and there are various numbers in the list.

Please somebody help me out with that...

 

[Momentman]

Rather than a VBA code, you can use an advanced filter

 

[kbj0109]

I want to create a button and assign the code in it...
I need it to make it simple, so my teammates can use it

 

[Michael M]

Duplicate post answered here
http://www.mrexcel.com/forum/excel-questions/851247-can-i-copy-paste-values-condition.html

 

[Momentman]

@Michael M: Seen

 

[Michael M]

@Momentman...see my previous post.....

 

[mikerickson]

You could use this UDF with this formula in B3
=ConcatIf(A3:A65563, ">10", A3:A65536, ",")

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
    stringsRange.Column - compareRange.Column)
     
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function