Select a set of cells in one column based on value in another

Question

Hi, I have a big set of data in excel of the following form

---

A   B
1   stuff1
6   stuff2
3   stuff3
1   stuff4
1   stuff5
7   stuff6
3   stuff7
2   stuff8
.   .
.   .
.   .
5   stuffn

---

and what i would like is some vba code that will select all the cells in B that have a "1" in column A - I will be using this set to do some tasks in another part of my code

any ideas?

Thanks

Answer 1

i dont know exactly what you are trying to do in the longrun, but for this step, you could do this in column C =if(a1=1,b1,"")
and then apply a filter on column c and pick the option non blank
then you can just select the whole column c

Answer 2

This should work:

Sub SelectCellsInColBBasedOnColA()
    Dim TheSheet As Worksheet
    If TypeOf ActiveSheet Is Worksheet Then
        Set TheSheet = ActiveSheet
    Else
        Exit Sub
    End If

    Dim Row As Integer
    Dim CellsToSelect As String
    For Row = 1 To TheSheet.Range("A" & CStr(TheSheet.Rows.Count)).End(xlUp).Row
        If TheSheet.Range("A" & CStr(Row)).Value = 1 Then
            If CellsToSelect <> "" Then CellsToSelect = CellsToSelect & ","
            CellsToSelect = CellsToSelect & "B" & CStr(Row)
        End If
    Next Row
    TheSheet.Range(CellsToSelect).Select
End Sub

Answer 3

There is always ADO.

'Reference: Microsost ActiveX n.n Object Library '
'but it is not necessary, Dim rs and cn as object '
'if you do not wish to use a reference '

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

'From: http://support.microsoft.com/kb/246335 '

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

'Substitute a name range for [Sheet1$] '
'or include a range of cells : [Sheet1&A1:C7] '
'F1 is field 1, because we have no header (HDR: No) '
strSQL = "SELECT * FROM [Sheet3$] " _
       & "WHERE F1=1"

rs.Open strSQL, cn

'Write out to another sheet '
Worksheets(2).Cells(2, 1).CopyFromRecordset rs