I would like to move a row to a different worksheet. I have come across the
following code to delete a cell. Can it be modified or is there a better
way....

Thanks
Bruce

_________________
Sub DeleteRowsContaining()
Dim r As Long
Dim ans As String
Dim c As Range
Dim lrow As Long

ans = InputBox("What string do you want rows to be deleted if they contain
it?")
Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = lrow To 1 Step -1
With Cells(r, 1)
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
.EntireRow.Delete
End If
End With
Next r
Application.ScreenUpdating = True

End Sub

Another option that I like to use with this kind of stuff is not move the data
at all. I apply Data|filter|autofilter to my range. Then I can filter the
column and see the status of each category.

I find it much more useful to keep my data in one spot. You can do lots more
things with it.

If you don't like that idea, try recording a macro when you apply the
autofilter, filter on each value, and copy the visible rows to the sheets you
want.

(But I wouldn't move them. I find filtering sufficient and easier for any next
step that comes up.)

Bruccce wrote:

I do want to REALLY MOVE but I want to move ALL that have the tested for
string.

Example.
I have a sheet with the following columns
Name, phone, results (this is over simplified!)
say results are "Not Interested" for several records.
I would like to move all of the records to sheet "Not_Interested"
same for calls like "Disconnected" to disconnected
etc...
And then that would leave me with a contacts that I still need to call.

Thanks
Bruce

"Dave Peterson" wrote in message
...
Really move (not copy?).

And is it just the first row where you find something.

I looked in column A of sheet1. I pasted into of sheet2 (in the next open
cell
in column A).

Option Explicit
Sub MoveRowsContaining()
Dim FoundCell As Range
Dim myRng As Range
Dim WhatToFind As String
Dim fromWks As Worksheet
Dim toWks As Worksheet
Dim destCell As Range

Set fromWks = Worksheets("sheet1")
Set toWks = Worksheets("sheet2")

WhatToFind = "Hi there"

With fromWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = Nothing

With myRng
Set FoundCell = .Cells.Find(what:=WhatToFind, _
LookIn:=xlValues, _
lookat:=xlPart, _
MatchCase:=False, _
after:=.Cells(.Cells.Count), _
searchorder:=xlNext)
If FoundCell Is Nothing Then
MsgBox "not found"
Else
With toWks
Set destCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

FoundCell.EntireRow.Copy _
Destination:=destCell

FoundCell.EntireRow.Delete 'really move???

End If
End With
End With
End Sub

Modify that .find command. I looked at xlpart (not xlwhole).

Bruccce wrote:

I would like to move a row to a different worksheet. I have come across
the
following code to delete a cell. Can it be modified or is there a better
way....

Thanks
Bruce

_________________
Sub DeleteRowsContaining()
Dim r As Long
Dim ans As String
Dim c As Range
Dim lrow As Long

ans = InputBox("What string do you want rows to be deleted if they
contain
it?")
Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = lrow To 1 Step -1
With Cells(r, 1)
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
.EntireRow.Delete
End If
End With
Next r
Application.ScreenUpdating = True

End Sub

--

Dave Peterson


--

Dave Peterson

I like the IDEA of filtering, but I use userforms and the filters dont seem
to work with UserForms (at least I have not figured out how to use filters
with forms....)
If I could find a way to filter and the userforms that would be ideal!

Thanks
Bruce



"Dave Peterson" wrote in message
...
Another option that I like to use with this kind of stuff is not move the
data
at all. I apply Data|filter|autofilter to my range. Then I can filter
the
column and see the status of each category.

I find it much more useful to keep my data in one spot. You can do lots
more
things with it.

If you don't like that idea, try recording a macro when you apply the
autofilter, filter on each value, and copy the visible rows to the sheets
you
want.

(But I wouldn't move them. I find filtering sufficient and easier for any
next
step that comes up.)

Bruccce wrote:

I do want to REALLY MOVE but I want to move ALL that have the tested for
string.

Example.
I have a sheet with the following columns
Name, phone, results (this is over simplified!)
say results are "Not Interested" for several records.
I would like to move all of the records to sheet "Not_Interested"
same for calls like "Disconnected" to disconnected
etc...
And then that would leave me with a contacts that I still need to call.

Thanks
Bruce

"Dave Peterson" wrote in message
...
Really move (not copy?).

And is it just the first row where you find something.

I looked in column A of sheet1. I pasted into of sheet2 (in the next
open
cell
in column A).

Option Explicit
Sub MoveRowsContaining()
Dim FoundCell As Range
Dim myRng As Range
Dim WhatToFind As String
Dim fromWks As Worksheet
Dim toWks As Worksheet
Dim destCell As Range

Set fromWks = Worksheets("sheet1")
Set toWks = Worksheets("sheet2")

WhatToFind = "Hi there"

With fromWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = Nothing

With myRng
Set FoundCell = .Cells.Find(what:=WhatToFind, _
LookIn:=xlValues, _
lookat:=xlPart, _
MatchCase:=False, _
after:=.Cells(.Cells.Count), _
searchorder:=xlNext)
If FoundCell Is Nothing Then
MsgBox "not found"
Else
With toWks
Set destCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1,
0)
End With

FoundCell.EntireRow.Copy _
Destination:=destCell

FoundCell.EntireRow.Delete 'really move???

End If
End With
End With
End Sub

Modify that .find command. I looked at xlpart (not xlwhole).

Bruccce wrote:

I would like to move a row to a different worksheet. I have come
across
the
following code to delete a cell. Can it be modified or is there a
better
way....

Thanks
Bruce

_________________
Sub DeleteRowsContaining()
Dim r As Long
Dim ans As String
Dim c As Range
Dim lrow As Long

ans = InputBox("What string do you want rows to be deleted if they
contain
it?")
Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = lrow To 1 Step -1
With Cells(r, 1)
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
.EntireRow.Delete
End If
End With
Next r
Application.ScreenUpdating = True

End Sub

--

Dave Peterson


--

Dave Peterson

Debra Dalgleish has some example code for splitting this kind of stuff up using
advanced filter. It works very nicely when you know the values you want to
extract.

http://www.contextures.com/excelfiles.html
(look for: Update Sheets from Master)

And if you don't know all the values to be extracted, you could use the advanced
filter to get the list:
http://www.contextures.com/xladvfilter01.html#FilterUR

But I'm not sure how the userform could interfere with the filters.

Bruccce wrote:

I like the IDEA of filtering, but I use userforms and the filters dont seem
to work with UserForms (at least I have not figured out how to use filters
with forms....)
If I could find a way to filter and the userforms that would be ideal!

Thanks
Bruce

"Dave Peterson" wrote in message
...
Another option that I like to use with this kind of stuff is not move the
data
at all. I apply Data|filter|autofilter to my range. Then I can filter
the
column and see the status of each category.

I find it much more useful to keep my data in one spot. You can do lots
more
things with it.

If you don't like that idea, try recording a macro when you apply the
autofilter, filter on each value, and copy the visible rows to the sheets
you
want.

(But I wouldn't move them. I find filtering sufficient and easier for any
next
step that comes up.)

Bruccce wrote:

I do want to REALLY MOVE but I want to move ALL that have the tested for
string.

Example.
I have a sheet with the following columns
Name, phone, results (this is over simplified!)
say results are "Not Interested" for several records.
I would like to move all of the records to sheet "Not_Interested"
same for calls like "Disconnected" to disconnected
etc...
And then that would leave me with a contacts that I still need to call.

Thanks
Bruce

"Dave Peterson" wrote in message
...
Really move (not copy?).

And is it just the first row where you find something.

I looked in column A of sheet1. I pasted into of sheet2 (in the next
open
cell
in column A).

Option Explicit
Sub MoveRowsContaining()
Dim FoundCell As Range
Dim myRng As Range
Dim WhatToFind As String
Dim fromWks As Worksheet
Dim toWks As Worksheet
Dim destCell As Range

Set fromWks = Worksheets("sheet1")
Set toWks = Worksheets("sheet2")

WhatToFind = "Hi there"

With fromWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = Nothing

With myRng
Set FoundCell = .Cells.Find(what:=WhatToFind, _
LookIn:=xlValues, _
lookat:=xlPart, _
MatchCase:=False, _
after:=.Cells(.Cells.Count), _
searchorder:=xlNext)
If FoundCell Is Nothing Then
MsgBox "not found"
Else
With toWks
Set destCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1,
0)
End With

FoundCell.EntireRow.Copy _
Destination:=destCell

FoundCell.EntireRow.Delete 'really move???

End If
End With
End With
End Sub

Modify that .find command. I looked at xlpart (not xlwhole).

Bruccce wrote:

I would like to move a row to a different worksheet. I have come
across
the
following code to delete a cell. Can it be modified or is there a
better
way....

Thanks
Bruce

_________________
Sub DeleteRowsContaining()
Dim r As Long
Dim ans As String
Dim c As Range
Dim lrow As Long

ans = InputBox("What string do you want rows to be deleted if they
contain
it?")
Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = lrow To 1 Step -1
With Cells(r, 1)
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
.EntireRow.Delete
End If
End With
Next r
Application.ScreenUpdating = True

End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson