Macro to select a list of values greater than or equal to a value

[Guest]

I am trying to write a macro that will search through a list of values (this
list will vary in size) for all values greater than or equal to a specific
value (say >=100). I want to activate all the cells containing those values
and copy them to another location. Can anyone help with this?

 

[Don Guillett]

Easiest is to use
data>filter>autofilter>custom

 

[Guest]

OK, I understand the use of the autofilter. Where I am confused is how to use
a macro to select those cells that the autofilter returns and copy them to
another location. Can you help me with that?

 

[Guest]

will copy values >=100 in column A and paste in column B.

Sub test()
Dim rng As Range
Dim c As Range
Dim rngCopy As Range

With ActiveSheet
Set rng = .Range(.Range("A1"), .Range("A65535").End(xlUp))
End With
For Each c In rng
If c.Value >= 100 Then
If rngCopy Is Nothing Then
Set rngCopy = c
Else
Set rngCopy = Union(rngCopy, c)
End If
End If
Next c
If Not rngCopy Is Nothing Then
rngCopy.Copy ActiveSheet.Range("B1")
End If

End Sub

 

[Don Guillett]

record a macor while doing it>clean it up and post your result for comments.

 

[Guest]

OK, I will give it a shot.

record a macor while doing it>clean it up and post your result for comments.

 

[Guest]

Thanks! I will try it! I appreciate the help!

will copy values >=100 in column A and paste in column B.

Sub test()
Dim rng As Range
Dim c As Range
Dim rngCopy As Range

With ActiveSheet
Set rng = .Range(.Range("A1"), .Range("A65535").End(xlUp))
End With
For Each c In rng
If c.Value >= 100 Then
If rngCopy Is Nothing Then
Set rngCopy = c
Else
Set rngCopy = Union(rngCopy, c)
End If
End If
Next c
If Not rngCopy Is Nothing Then
rngCopy.Copy ActiveSheet.Range("B1")
End If

End Sub

 

[Guest]

I tried this and it works great! One more question: if I also want to copy
the values in the 2 columns to the right of each cell that contains a value
greater than or equal to 100, how would I do that? Would I use Offset?

 

[Guest]

Doogie,

You would do it somewhat like this:

'Find values >=100 in Col A, copy that and the 2 cells its right. paste in
Col D
Sub test()
Dim rng As Range
Dim c As Range
Dim rngCopy As Range

With ActiveSheet
Set rng = .Range(.Range("A1"), .Range("A65535").End(xlUp))
End With
For Each c In rng
If c.Value >= 100 Then
If rngCopy Is Nothing Then
Set rngCopy = c.Resize(1, 3)
Else
Set rngCopy = Union(rngCopy, c.Resize(1, 3))
End If
End If
Next c
If Not rngCopy Is Nothing Then
rngCopy.Copy ActiveSheet.Range("D1")
End If

End Sub

 

[Guest]

Thank you very much! This will help me out a great deal with a daily report
that I prepare at my plant. I was not familiar with the Union or Resize
functions. This is what I like about these discussion groups, they are
fantastic learning experiences.