Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Upgrade from Excel 97 to 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 V's Excel 2000? | Excel Worksheet Functions | |||
quattro pro converter Excel 2003 | New Users to Excel | |||
Saving Customized Toolbars in Excel 2003 | Excel Discussion (Misc queries) | |||
Saving a Excel 97 file into Excel 2003 file | Excel Discussion (Misc queries) |