Select Range by Condition in Cell

[mucah!t]

Hello all,

I need to select all ranges A:C where there is a "TRUE" mentioned in A
The following code only selects the first occurance [from the bottom]
Any ideas?

Sub test()
   Dim lastrow As Long, icell As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For icell = 1 To lastrow
    If Range("A" & icell) = True Then
        
        Range("A" & icell, "C" & icell).Select
    
    Else
    End If
Next icell
End Sub

 

[balajayan]

Hi,
Please try the below code and revert.

    rowend = Range("A" & Rows.Count).End(xlUp).Row
    Range("A5:C" & rowend).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$5:$C$" & rowend).AutoFilter Field:=1, Criteria1:="TRUE"
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.AutoFilter

Hope this satisfies your requirement.

 

[mucah!t]

I'd like cells A:C to be selected where the cell in A has a "TRUE"

 

[mucah!t]

hello balajayan,

This one works almost.
the only problem with it is, that it also selects A5 [the first row] while it is "false" iso "true"

 

[CharlesChuckieCharles]

I'd like cells A:C to be selected where the cell in A has a "TRUE"

So like this then

Sub test()
Dim lastrow As Long, icell As Long
Dim sRanges As String
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For icell = 1 To lastrow
    If Range("A" & icell) = True Then
        If sRanges = "" Then
        sRanges = "A" & Trim(Str(icell)) & ":C" & Trim(Str(icell))
        Else
        sRanges = sRanges & ",A" & Trim(Str(icell)) & ":C" & Trim(Str(icell))
        End If
 
      '  Range("A" & icell, "C" & icell).Select
 
    End If
Next icell
If sranges <> "" Then
    Range(sRanges).Select
End If
 
End Sub

 

[mucah!t]

Hello Charles,

It keeps on giving an error on line:

Range(sRanges).Select

 

[CharlesChuckieCharles]

maybe you need to state the activesheet.range(....

To test .."test" i use developer tab/ macros/run macro
works everytime.

So if this function is being called by other functions you will need to take account of the activesheet that it will act upon

 

[balajayan]

Hi,
Slightly modified, please try the below code:

    rowend = Range("A" & Rows.Count).End(xlUp).Row
    Range("A5:C" & rowend).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$5:$C$" & rowend).AutoFilter Field:=1, Criteria1:="TRUE"
    If Range("A5").Value = False Then
        Range("A6:C" & rowend).Select
    End If
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.AutoFilter

 

[mucah!t]

Thanks you guys, for your help.
I got it working now!