Find Function

[DejaVu]

I modified some code from Aaron Blood and Ivan F Moala at:
http://www.ozgrid.com/forum/showthre...t=27240&page=1

This is a find function that I am using to find all rows that contain "Sales" in column J, and move them from Sheet1 to Sheet2. This function works perfectly, but I'm wanting it do do a little more. If I can figure it out, I'd like it to delete the rows from Sheet1 that it pastes into Sheet2. I can write a little procedure after this function to do this, but I was wondering if it is possible to do inside the function.

This is what I'm using right now outside of the function, and it works fine:
For Each tmpRng In rngSls
If ActiveCell.Value = "Sales" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(1, 0).Select
Next

Any better solutions or ideas are greatly appreciated.

DejaVu

[Toppers]

Hi,
Based on the routine you referenced, try this:

Sub test()
Dim rng As Range
Set rng = Find_Range("Sales", Range("a1:a50"))
rng.Copy Worksheets("sheet2").Range("a1") 'Copy date
rng.EntireRow.Delete ' Delete original rows
End Sub


HTH

"DejaVu" wrote:

>
> I modified some code from Aaron Blood and Ivan F Moala at:
> http://www.ozgrid.com/forum/showthre...t=27240&page=1
>
> This is a find function that I am using to find all rows that contain
> "Sales" in column J, and move them from Sheet1 to Sheet2. This
> function works perfectly, but I'm wanting it do do a little more. If I
> can figure it out, I'd like it to delete the rows from Sheet1 that it
> pastes into Sheet2. I can write a little procedure after this function
> to do this, but I was wondering if it is possible to do inside the
> function.
>
> This is what I'm using right now outside of the function, and it works
> fine:
> For Each tmpRng In rngSls
> If ActiveCell.Value = "Sales" Then
> ActiveCell.EntireRow.Delete
> End If
> ActiveCell.Offset(1, 0).Select
> Next
>
> Any better solutions or ideas are greatly appreciated.
>
> DejaVu
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=385313
>
>

[Jim Thomlinson]

This code is a whole pile more efficient than the code you are using...

Sub Test()
Call CopyCells("Sales")
End Sub

Sub CopyCells(ByVal strWordToFind As String)
Dim rngFirst As Range
Dim rngCurrent As Range
Dim rngFoundCells As Range
Dim rngToSearch As Range
Dim wksToSearch As Worksheet
Dim wksToPaste As Worksheet
Dim rngToPaste As Range

Set wksToSearch = Sheets("Sheet1")
Set wksToPaste = Sheets("Sheet2")
Set rngToSearch = wksToSearch.Cells
Set rngToPaste = wksToPaste.Range("A65536").End(xlUp).Offset(1, 0)
Set rngCurrent = rngToSearch.Find(strWordToFind, , , xlWhole)
If rngCurrent Is Nothing Then
MsgBox strWordToFind & " was not found"
Else
Set rngFirst = rngCurrent
Set rngFoundCells = rngCurrent.EntireRow
Do
Set rngFoundCells = Union(rngCurrent.EntireRow, rngFoundCells)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngFirst.Address = rngCurrent.Address
rngFoundCells.Copy rngToPaste
rngFoundCells.Delete
End If
End Sub
--
HTH...

Jim Thomlinson


"DejaVu" wrote:

>
> I modified some code from Aaron Blood and Ivan F Moala at:
> http://www.ozgrid.com/forum/showthre...t=27240&page=1
>
> This is a find function that I am using to find all rows that contain
> "Sales" in column J, and move them from Sheet1 to Sheet2. This
> function works perfectly, but I'm wanting it do do a little more. If I
> can figure it out, I'd like it to delete the rows from Sheet1 that it
> pastes into Sheet2. I can write a little procedure after this function
> to do this, but I was wondering if it is possible to do inside the
> function.
>
> This is what I'm using right now outside of the function, and it works
> fine:
> For Each tmpRng In rngSls
> If ActiveCell.Value = "Sales" Then
> ActiveCell.EntireRow.Delete
> End If
> ActiveCell.Offset(1, 0).Select
> Next
>
> Any better solutions or ideas are greatly appreciated.
>
> DejaVu
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=385313
>
>

[Tom Ogilvy]

Is this what you are referring to: (only reference ot Moala or Blood in the
thread you posted).

Hi LTT

While in your VBE, press F1 and type in Find

Have a look @ the ensuing help files
Your help files are the best place to search for this.
Then if you require further assistance on something specific, post on that..
__________________
Kind Regards,
Ivan F Moala From the City of Sails

always a good Idea to consult the help file.

--
Regards,
Tom Ogilvy

"DejaVu" <[email protected]> wrote in
message news:[email protected]...
>
> I modified some code from Aaron Blood and Ivan F Moala at:
> http://www.ozgrid.com/forum/showthre...t=27240&page=1
>
> This is a find function that I am using to find all rows that contain
> "Sales" in column J, and move them from Sheet1 to Sheet2. This
> function works perfectly, but I'm wanting it do do a little more. If I
> can figure it out, I'd like it to delete the rows from Sheet1 that it
> pastes into Sheet2. I can write a little procedure after this function
> to do this, but I was wondering if it is possible to do inside the
> function.
>
> This is what I'm using right now outside of the function, and it works
> fine:
> For Each tmpRng In rngSls
> If ActiveCell.Value = "Sales" Then
> ActiveCell.EntireRow.Delete
> End If
> ActiveCell.Offset(1, 0).Select
> Next
>
> Any better solutions or ideas are greatly appreciated.
>
> DejaVu
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=385313
>

[DejaVu]

Thanks for all the replies, but I found that function to be very fast and efficient for what I need it for. After that function is done, this is the bit of code that immediately follows it:
Worksheets("Sheet1").Range("J1").Select
Set rngSls = Range("J1", Selection.End(xlDown))
For Each tmpRng In rngSls
If ActiveCell.Value = "Sales" Then
Do Until ActiveCell.Value <> "Sales"
i = ActiveCell.Row
Cells(i, 10).EntireRow.Delete
Loop
End If
ActiveCell.Offset(1, 0).Select
Next

This code works perfectly for deleting the rows from Sheet1 after the function has copied them to Sheet2. If I was not specific enough, I'm sorry for the confusion. My only question was: Is there a way to use that existing function (from my link above) to make it delete the rows out of Sheet1 that it has copied to Sheet2?

Here is what I used to call the function and have it copy rows to Sheet2:
Find_Range("Sales", Columns("J"), xlFormulas, xlWhole).EntireRow.Copy Range("Sheet2!A2")

Everything works fine now, so I dont know whether I should change or not, but I wanted to know if this was the "best" way to accomplish this task.

Thanks,

DejaVu

[Tom Ogilvy]

Jim gave you a specific code example of how you might want to do it. If you
are happy with what you have and it works, then it is a matter of your
choice.

--
Regards,
Tom Ogilvy

"DejaVu" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for all the replies, but I found that function to be very fast
> and efficient for what I need it for. After that function is done,
> this is the bit of code that immediately follows it:
> Worksheets("Sheet1").Range("J1").Select
> Set rngSls = Range("J1", Selection.End(xlDown))
> For Each tmpRng In rngSls
> If ActiveCell.Value = "Sales" Then
> Do Until ActiveCell.Value <> "Sales"
> i = ActiveCell.Row
> Cells(i, 10).EntireRow.Delete
> Loop
> End If
> ActiveCell.Offset(1, 0).Select
> Next
>
> This code works perfectly for deleting the rows from Sheet1 after the
> function has copied them to Sheet2. If I was not specific enough, I'm
> sorry for the confusion. My only question was: Is there a way to use
> that existing function (from my link above) to make it delete the rows
> out of Sheet1 that it has copied to Sheet2?
>
> Here is what I used to call the function and have it copy rows to
> Sheet2:
> Find_Range("Sales", Columns("J"), xlFormulas, xlWhole).EntireRow.Copy
> Range("Sheet2!A2")
>
> Everything works fine now, so I dont know whether I should change or
> not, but I wanted to know if this was the "best" way to accomplish this
> task.
>
> Thanks,
>
> DejaVu
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=385313
>

[DejaVu]

Thanks Tom, and thanks Jim for you help on this.

Tom - I understand Jim gave me a specific code example, and I'm greatful for his help. I found a solution that worked, and I also tried Jim solution and they both worked about equally as well (as far as I could tell). I guess what I was asking was; Whats the "best" way to accomplish that task. I realize there are many roads a person could take to get to the same destination, but I was just inquiring about which way is the "best"? My apologies if I confused you.


DejaVu

Update: Jim - I tried my version of the code on an Excel 2000 machine, and it errored out within the function. Your version worked much better in this instance. thanks again..

Also Tom - I was having a problem with xl2003 xlPasteColumnWidths and xl2000 xlColumnWidths. Then I found an old thread that you posted in here ... I used the hardcoded value 8 like you suggested and it worked in both version. Thanks a ton!!