2 open workbooks - find 1 delete from other

[davie1982]

Hiya again.

I like this forum as you can tell :P

I have 2 open workbooks. I've picked up the filenames in VB.

One of them is strRemitN

Another one is strStateM

In strRemit i have invoice numbers (OP/I123456) in column A.

in strStateM the invoice numbers are in column C

How would i go around, in VBA, to search for the first invoice number from strRemitN to strStateM, delete the row, then go to the next, all the way to the bottom?

To help with the stuff i've already got... i've worked out the last row.

Dim intLRow as integer
intLRow = Range("A65536").End(xlUp).Row

Can anyone either help me on this or point me in the right direction please?

Thanks.

 

[jindon]

Don't loop

Sub RemitCheck()
Dim lngLastRow As Long, i As Long

Dim str2ndWbName As String
Dim strWBName As String
strWBName = ActiveWorkbook.Name
str2ndWbName = Sheets("code").Range("A4").Formula
Workbooks.Open (Sheets("Code").Range("A1").Formula)

Windows(str2ndWbName).Activate
Application.ScreenUpdating = False
lngLastRow = Range("A65535").End(xlUp).Row

    Columns("H").Insert Shift:=xlToRight

    With Range("H5:H" & lngLastRow)
        .FormulaR1C1 = _
        "=IF(COUNTIF('[" & strWBName & "]Remittance'!C1,RC[-5])>0, 1, """")"
        On Error Resume Next
        .SpecialCells(-4123,1).EntireRow.Delete
    End With

    Columns("H").Delete Shift:=xlToLeft

    Application.ScreenUpdating = True
MsgBox prompt:="All invoice from the remit have been taken off of the Statement", _
        Title:="Complete"

End Sub

 

[davie1982]

This one i've got here is instant, even with 1200 invoice numbers!

Application.Screenupdating = False
Dim lngPasteRow As Long
lngPasteRow = 2
    Columns("H").Insert Shift:=xlToRight
    With Range("H5:H" & lngLastRow)
        .FormulaR1C1 = _
        "=IF(COUNTIF('[" & strWBName & "]Remittance'!C1,RC[-5])>0, 1, """")"
    On Error Resume Next
    .SpecialCells(-4123, 1).EntireRow.Copy Sheets("Remitted").Rows(lngPasteRow)
    .SpecialCells(-4123, 1).EntireRow.Delete
    lngPasteRow = lngPasteRow + 1
    End With

    Columns("H").Delete Shift:=xlToLeft
Application.Screenupdating = True

Thanks for all your help guys, this way here i get no errors and all the invoices are kept in the remittance spreadsheet.