Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Alcide
 
Posts: n/a
Default 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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Alcide
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Upgrade from Excel 97 to 2003 J. Freed Excel Discussion (Misc queries) 0 March 17th 05 03:49 PM
Excel 2003 V's Excel 2000? Ket Excel Worksheet Functions 2 March 9th 05 01:05 PM
quattro pro converter Excel 2003 dr88363 New Users to Excel 0 February 27th 05 06:00 PM
Saving Customized Toolbars in Excel 2003 MIKE MEDLIN Excel Discussion (Misc queries) 5 January 13th 05 03:03 AM
Saving a Excel 97 file into Excel 2003 file Wil Excel Discussion (Misc queries) 1 December 14th 04 12:51 AM


All times are GMT +1. The time now is 02:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"