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

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

From stackoverflow

• 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

• 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
  

• 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