Range of non-adjucent columns and error in deleting last line therein

[Tango_Bravo]

Dear All,


I have a lengthy worksheet, which is a register of issued invoices. Each row contains a separate invoice and each column provides different details for each separate invoice. If I want to cancel an invoice (because data is incorrect) I use a sub which deletes last issued invoice - i.e. the last line in the table.


Columns A keeps tracking number of type A invoices, column B keep tracking number of type B invoices. I want to introduce a third type of invoice in the table and column D is supposed to keep track of the consecutive number for this type of invioice.

After introducing the third type of invoices, when testing cancellation (i.e. deletion of last line) I the following error:

Run-time error 1004: Delete method or Range classified failed

here is the code:

    Set v1 = .Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    Set v2 = .Range("D:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    Set r = Union(v1, v2)

    str1 = r.Value


If MsgBox("This will undo last validation, are you sure?", vbOKCancel) = vbCancel Then Exit Sub
flag = False

        If Not r Is Nothing Then
        r.EntireRow.Delete
        flag = True
        End If

And the line that brings the issue is the following:

        r.EntireRow.Delete

Help will be much appreciated.

 

[Eric W]

You can't use the .EntireRow.Delete method on a disjoint range, which you create using the Union function. You might try something like:

        If v1.Row > v2.Row Then
            v1.EntireRow.Delete
        Else
            v2.EntireRow.Delete
        End If

which will delete the furthest row down, or if they are on the same row, that row.