Isolate rows based on highlight (Excel 2003)
 

Hello All,

I am trying to select some rows that are highlighted and then take those
highlighted rows and place in a new worksheet. I tried using Find by Format
and I got a list in the dialogue of every cell that has that format, which is
a step in the right direction. But I need to somehow "grab" (copy and paste)
the highlighted rows and place in a separate worksheet.

I also made the range into a list, but there aren't any filter by formats
there.

BTW, these rows were highlighted manually, not by conditional formatting.

Any ideas?

Thanks in advance for the assistance.

I think you're going to have to do multiple finds:

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim AllCells As Range
Dim myCell As Range
Dim FirstAddress As String

With Application.FindFormat.Font
.FontStyle = "Bold"
.Subscript = False
End With

With Worksheets("Sheet1")
Set FoundCell = .Cells.Find(What:="", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True)

If FoundCell Is Nothing Then
MsgBox "not found"
Else
FirstAddress = FoundCell.Address
Do
If AllCells Is Nothing Then
Set AllCells = FoundCell
Else
Set AllCells = Union(FoundCell, AllCells)
End If
Set FoundCell = .Cells.Find(What:="", After:=FoundCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If

End With

For Each myCell In AllCells.Cells
'do what you want here
MsgBox myCell.Address
Next myCell
End Sub

This is based on VBA's sample code for .Find. But .findNext doesn't seem to
remember the format options--so it does another .find.


Alcide wrote:

Hello All,

I am trying to select some rows that are highlighted and then take those
highlighted rows and place in a new worksheet. I tried using Find by Format
and I got a list in the dialogue of every cell that has that format, which is
a step in the right direction. But I need to somehow "grab" (copy and paste)
the highlighted rows and place in a separate worksheet.

I also made the range into a list, but there aren't any filter by formats
there.

BTW, these rows were highlighted manually, not by conditional formatting.

Any ideas?

Thanks in advance for the assistance.

--

Dave Peterson

Thanks Dave!

I am going to give it a test before the end of the day and let you know how
it works.

That is some serious coding you did. I knew that it wasn't going to be easy.

Will report back soon.

"Dave Peterson" wrote:

I think you're going to have to do multiple finds:

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim AllCells As Range
Dim myCell As Range
Dim FirstAddress As String

With Application.FindFormat.Font
.FontStyle = "Bold"
.Subscript = False
End With

With Worksheets("Sheet1")
Set FoundCell = .Cells.Find(What:="", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True)

If FoundCell Is Nothing Then
MsgBox "not found"
Else
FirstAddress = FoundCell.Address
Do
If AllCells Is Nothing Then
Set AllCells = FoundCell
Else
Set AllCells = Union(FoundCell, AllCells)
End If
Set FoundCell = .Cells.Find(What:="", After:=FoundCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If

End With

For Each myCell In AllCells.Cells
'do what you want here
MsgBox myCell.Address
Next myCell
End Sub

This is based on VBA's sample code for .Find. But .findNext doesn't seem to
remember the format options--so it does another .find.


Alcide wrote:

Hello All,

I am trying to select some rows that are highlighted and then take those
highlighted rows and place in a new worksheet. I tried using Find by Format
and I got a list in the dialogue of every cell that has that format, which is
a step in the right direction. But I need to somehow "grab" (copy and paste)
the highlighted rows and place in a separate worksheet.

I also made the range into a list, but there aren't any filter by formats
there.

BTW, these rows were highlighted manually, not by conditional formatting.

Any ideas?

Thanks in advance for the assistance.

--

Dave Peterson