Comments on: Selecting range of data based on values in a particular column
http://ask.metafilter.com/112880/Selecting-range-of-data-based-on-values-in-a-particular-column/
Comments on Ask MetaFilter post Selecting range of data based on values in a particular columnThu, 29 Jan 2009 12:01:16 -0800Thu, 29 Jan 2009 12:01:16 -0800en-ushttp://blogs.law.harvard.edu/tech/rss60Question: Selecting range of data based on values in a particular column
http://ask.metafilter.com/112880/Selecting-range-of-data-based-on-values-in-a-particular-column
ExcelFilter: How can I select a range of data using VBA in Excel based on the values in a particular column? <br /><br /> I've got a report that exports its data to excel in 4 columns: Name, Crew Station, Hours in last 30 days and Hours in last 60 days. Crew Station, located in Column B, has two values: PILOT and AIRCREW. The macro sorts the data by Column B, with PILOT rows on top and AIRCREW values on bottom. I want to select the AIRCREW range of data (all four columns) only.post:ask.metafilter.com,2009:site.112880Thu, 29 Jan 2009 11:02:59 -0800squorchexcelvbaselectcolumnvaluesBy: Doofus Magoo
http://ask.metafilter.com/112880/Selecting-range-of-data-based-on-values-in-a-particular-column#1622179
Try this. It assumes that you have the worksheet in which the data have been populated is the active sheet, and that your data starts in row #2:<br>
<br>
<code>Public Sub getAircrew()<br>
<br>
Dim nRow As Long<br>
Dim nStart As Long, nEnd As Long<br>
<br>
' Figure out where the "AIRCREW" data starts.<br>
For nRow = 1 To 65536<br>
If Range("B" & nRow).Value = "AIRCREW" Then<br>
nStart = nRow<br>
Exit For<br>
End If<br>
Next nRow<br>
<br>
' Figure out where the "AIRCREW" data ends.<br>
For nRow = nStart To 65536<br>
If Range("B" & nRow).Value <> "AIRCREW" Then<br>
nEnd = nRow<br>
Exit For<br>
End If<br>
Next nRow<br>
nEnd = nEnd - 1<br>
<br>
Range("A" & nStart & ":D" & nEnd).Select<br>
<br>
End Sub<br>
</></code>comment:ask.metafilter.com,2009:site.112880-1622179Thu, 29 Jan 2009 12:01:16 -0800Doofus MagooBy: squorch
http://ask.metafilter.com/112880/Selecting-range-of-data-based-on-values-in-a-particular-column#1622204
That works great... I was also working on a brute-force version, but you beat me to the punch. Thanks!comment:ask.metafilter.com,2009:site.112880-1622204Thu, 29 Jan 2009 12:14:53 -0800squorchBy: pompomtom
http://ask.metafilter.com/112880/Selecting-range-of-data-based-on-values-in-a-particular-column#1622885
Just cos I was wondering... the following should be a bit quicker and doesn't require the sort.<br>
<br>
<pre><br>
Sub SelectionThing()<br>
Dim FinalSelection As Range<br>
Cells(1, 1).Select<br>
For Each c In Intersect(ActiveSheet.UsedRange, Range("B:B"))<br>
If c.Value = "Aircrew" Then<br>
If FinalSelection Is Nothing Then<br>
Set FinalSelection = Range(Cells(c.Row, 2), Cells(c.Row, 6))<br>
Else<br>
Set FinalSelection = Union(FinalSelection, Range(Cells(c.Row, 2), Cells(c.Row, 6)))<br>
End If<br>
End If<br>
Next c<br>
If Not FinalSelection Is Nothing Then FinalSelection.Select<br>
End Sub<br>
</pre>comment:ask.metafilter.com,2009:site.112880-1622885Thu, 29 Jan 2009 19:19:49 -0800pompomtom