Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hello Steved.
Can you adapt the following to suit your needs? (Code copied from one of the frequent posters. I think maybe Dave Peterson, but I'm not sure.) Private Sub tester() Application.ScreenUpdating = False Dim r As Range With ActiveSheet ..AutoFilterMode = False Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp)) 'Debug.Print r.Address(external:=True) If Application.CountIf(r, "No AVL UNIT") = 0 Then Exit Sub ..Columns("A:A").AutoFilter Field:=1, Criteria1:="No AVL UNIT" Set r = r.SpecialCells(xlCellTypeVisible) ..AutoFilterMode = False r.EntireRow.Copy Destination:=Worksheets("No AVL").Range("a2") r.EntireRow.delete End With Application.ScreenUpdating = True End Sub "Steved" wrote: Hello from Steved Please how do I change the bottom to do 1. find "No AVL UNIT" in Column I in Sheet Named "Trip missed" 2 Once found move to Sheet Named "No AVL" and then Delete the Row in Sheet "Trip missed" which it had come from. Thankyou Sub CopyYRows() Dim curRowNo As Long Dim lastInsertRowNo As Long Dim lastRowNo As Long lastInsertRowNo = Sheets("No AVL").Range("A65536").End(xlUp).Row + 1 curRowNo = 8 'Start row number in Sheet 1 lastRowNo = Sheets("Trip missed").Range("I65536").End(xlUp).Row While curRowNo <= lastRowNo With Sheets("Trip missed").Cells(curRowNo, "I") If Not IsEmpty(.Value) And IsNumeric(.Value) Then .EntireRow.Copy Destination:= _ Sheets("No AVL").Range("A" & lastInsertRowNo) .EntireRow.Delete lastRowNo = lastRowNo - 1 lastInsertRowNo = lastInsertRowNo + 1 Else curRowNo = curRowNo + 1 End If End With Wend Sheets("No AVL").Activate End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Delete values in sheet 2 that arre found in sheet 1 | Excel Discussion (Misc queries) | |||
If ID # on one sheet isn't found on another... | Excel Worksheet Functions | |||
how can I format sheet 1 if the value is found in sheet 2? | New Users to Excel | |||
move data/row when certain words are found in cell | Excel Discussion (Misc queries) | |||
If found, then copy and paste contents, otherwise move on | Excel Programming |