Moving a row based on content of a cell to a diff worksheet

  • Thread starter Thread starter Bruccce
  • Start date Start date
B

Bruccce

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.)
 
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
 
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.
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
Click to expand...
 
Back
Top