conditional range selection VBA

[mjrofra]

Hi!

This is my problem:

If I have numbers in column A and I need to select data in that range that is greater than 50 with VBA, which is the easiest way to do it.

 

[Tom Urtis]

How about recording a macro using AutoFilter for custom, Is Greater Than for 50. Then select the filtered range below the header row, hit F5, then Special > Visible Cells Only > OK.

That will select what you filtered but the issue is what you want to do with those cells after that, where you probably don't even need to select them in the first place.

 

[VoG]

You shouldn't need to select those cells in order to work with them but try

Sub Bigger50()
Dim LR As Long, i As Long, r As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("A" & i).Value > 50 Then
        If r Is Nothing Then
            Set r = Range("A" & i)
        Else
            Set r = Union(r, Range("A" & i))
        End If
    End If
Next i
r.Select
End Sub

 

[mjrofra]

Thank you very much Tom and VoG.

I think you´re right, I don´t need to select those cells (I supose) what i need to do with those cell is to copy in another range (very simple, but I don´t know how).

Appreciate your help.

 

[VoG]

Try this: change B1 as needed:

Sub Bigger50()
Dim LR As Long, i As Long, r As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("A" & i).Value > 50 Then
        If r Is Nothing Then
            Set r = Range("A" & i)
        Else
            Set r = Union(r, Range("A" & i))
        End If
    End If
Next i
r.Copy Destination:=Range("B1")
End Sub

However, using a filter is likely to be quicker.

 

[mjrofra]

Thank you very mucho VoG, the code works perfect and I can learn a lot with it.

VoG, excuse me, just one more question, if column A is my criteria and I need to copy data from column A to column C to any other range, what do I need to change in the code.

 

[VoG]

You just need to change B1 (shown in red) to the top of the range that you want to copy to.