Find and select multiple rows

Question

How to search a column for text and select all columns and rows that match the search text?

Example table:

ColA ColB ColC ColD Row1 Bob Row2 Jane Row3 Joe Row4 Joe Row5 Jack Row6 Jack Row7 Jack Row8 Peter Row9 Susan 

So marco is looking for “Jack,” then he must select all the Row5-7 in ColA-D.

Answer 1

In the end, I did something a little different from my question.

This macro will search for each row in the source sheet and copy it to the target sheet, which is the parameter. Data does not need to be sorted, but it increases the runtime of marco. You can fix this by comparing the previous line, looking for a different value than before. The target list must exist and any data will be overwritten (cannot be undone!)

 Sub Search_SelectAndCopy(sheetname As String) Dim SheetData As String Dim DataRowNum As Integer, SheetRowNum As Integer SheetData = "name of sheet to search in" //' Source sheet DataRowNum = 2 //' Begin search at row 2 SheetRowNum = 2 //' Begin saving data to row 2 in "sheetname" //' Select sheetname, as its apparently required before copying is allowed ! Worksheets(SheetData).Select //' Search and copy the data While Not IsEmpty(Cells(DataRowNum, 2)) //' Loop until column B gets blank //' Search in column B for our value, which is the same as the target sheet name "sheetname" If Range("B" & CStr(DataRowNum)).Value = sheetname Then //' Select entire row Rows(CStr(DataRowNum) & ":" & CStr(DataRowNum)).Select Selection.Copy //' Select target sheet to store the data "sheetname" and paste to next row Sheets(sheetname).Select Rows(CStr(SheetRowNum) & ":" & CStr(SheetRowNum)).Select ActiveSheet.Paste SheetRowNum = SheetRowNum + 1 //' Move to next row //' Select source sheet "SheetData" so searching can continue Sheets(SheetData).Select End If DataRowNum = DataRowNum + 1 //' Search next row Wend //' Search and copying complete. Lets make the columns neat Sheets(sheetname).Columns.AutoFit //' Finish off with freezing the top row Sheets(sheetname).Select Range("A2").Select ActiveWindow.FreezePanes = True End Sub 

Remove each pair // before use.

Answer 2

It is not as beautiful as it could be, but it does its job:

 Public Sub SelectMultiple() Dim wbkthis As Workbook Dim shtthis As Worksheet Dim rngThis As Range Dim rngFind As Range Dim firstAddress As String Dim addSelection As String Set wbkthis = ThisWorkbook Set shtthis = wbkthis.Worksheets("Sheet1") // Set our range to search Set rngThis = shtthis.Range("B2", "B10") // Loop through it With rngThis // Find our required text Set rngFind = .Find("Jack") // If we find it then... If Not rngFind Is Nothing Then firstAddress = rngFind.Address // Take a note of where we first found it addSelection = addSelection & rngFind.Address & "," // Add the cell range to our selection // Loop through the rest of our range and find any other instances. Do Set rngFind = .FindNext(rngFind) addSelection = addSelection & rngFind.Address & "," Loop While Not rngFind Is Nothing And rngFind.Address <> firstAddress End If End With // Trim the last comma from our string addSelection = Mid(addSelection, 1, Len(addSelection) - 1) shtthis.Range(addSelection).Rows.Select // Select our rows! Set rngThis = Nothing Set shtthis = Nothing Set wbkthis = Nothing End Sub 

PLEASE NOTE: I replaced the VBA comment with a C # // comment to make this code more understandable.