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

If Foundrows is nothing then
set foundrows = cell
else
set foundrows = union(cell, foundrows)
end if

'you can use .entirerow later

if foundrows is nothing then
'do nothing
else
foundrows.entirerow.select '???or whatever you want here.
end if



Mick wrote:

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

--

Dave Peterson

You sir are a steely-eyed Excel man. :)

Thanks!

"Dave Peterson" wrote:

If Foundrows is nothing then
set foundrows = cell
else
set foundrows = union(cell, foundrows)
end if

'you can use .entirerow later

if foundrows is nothing then
'do nothing
else
foundrows.entirerow.select '???or whatever you want here.
end if



Mick wrote:

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

--

Dave Peterson

Excellent music. Oh, wait. That was Steely Dan.



Mick wrote:

You sir are a steely-eyed Excel man. :)

Thanks!

"Dave Peterson" wrote:

If Foundrows is nothing then
set foundrows = cell
else
set foundrows = union(cell, foundrows)
end if

'you can use .entirerow later

if foundrows is nothing then
'do nothing
else
foundrows.entirerow.select '???or whatever you want here.
end if



Mick wrote:

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

--

Dave Peterson


--

Dave Peterson