Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Range to an existing Range in a loop?
How can I add a range of cells (a whole row) to an existing range, and do
this in a loop? I want to search for cells containing a specific string, then append all of the cells in those rows to a Range object, the select the resulting Range. Sub FindAndSelect() Dim foundRows As Range Dim cell As Range Dim lnLastRow As Long Dim lnLastCol As Long Dim target As String Dim lastCellAddr As String ' Get the address of the last cell containing data Range("A1").Select On Error Resume Next lnLastRow = cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row lnLastCol = cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column lastCellAddr = cells(lnLastRow, lnLastCol).Address ' Get the string to search for target = InputBox("Enter search string") If target = "" Then Exit Sub End If ' Find all rows containing the search string and add them to the selection With Worksheets(1).Range("a1:" + lastCellAddr) Set cell = .Find(target, LookIn:=xlValues, Lookat:=xlPart) If Not cell Is Nothing Then firstAddress = cell.Address Do ' <<< THIS IS WHERE I NEED HELP ' Append all of the cells in cell.EntireRow to the foundRows Range ' foundRows = foundRows + cell.EntireRow Set foundRows = Union(foundRows, cell.EntireRow) Set cell = .FindNext(cell) Loop While cell.Address < firstAddress End If End With ' Select all of the rows that contained the search string foundRows.Select End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop through a range | Excel Programming | |||
For Each Loop w/ a range | Excel Programming | |||
loop in range | Excel Programming | |||
Loop in a Range | Excel Programming | |||
Loop through a range | Excel Programming |