VBA - Issue with index (I think) - Please Help

[Valou Choc]

Hi Guys,

I have an issue with a VBA macro that I am writing. I will try to explain it as good as I can, feel free to ask me questions.

I have a Database with about 3000 lines and I try to cut some of the lines and paste them into another tab.
In TablColG, I have all the data from my database. In List_IndDesc_Excl, I have all the criteria of the lines I would like to remove from the database.
If in TabColG, the element contains one of the element in List_IndDesc_Excl, I cut this line and paste it into a tab called "Deleted".

In my Database and Tab Deleted, I have 6 rows of header before finding the data.

Here is the code I wrote:



For Lig = LBound(TablColG) + 1 To UBound(TablColG)
For cpt = LBound(List_IndDesc_Excl) To UBound(List_IndDesc_Excl)
If InStr(TablColG(Lig), List_IndDesc_Excl(cpt)) <> 0 Then
ActiveSheet.Range("G" & Lig + 6 - LigDelete).EntireRow.Cut Destination:=Sheets("Deleted").Range("A" & LigDelete + 7)
Range("G" & Lig + 6 - LigDelete).EntireRow.Delete
LigDelete = LigDelete + 1
Else
Range("G" & Lig + 6 - LigDelete).EntireRow.Select
End If
Next cpt
Next Lig



Lig is going through my database; cpt through the list of criteria; If the criteria is met, it select the row number Lig+6-Ligdelete (Cause I have 6 rows of header and I remove the lines already cut) and I paste this line in my other tab line Ligdelete+7 (Due to the header as well and the lines already cut/paste)
I wrote an else part to this if cause I wanted to make sure that the macro is actually going through all the lines and it does.
This is not working properly, it does check all elements, it finds all the one to delete BUT from row 200 around, it starts cutting the row located 2 rows above the one it should actually remove.
I guess I have an issue with my index, however I do not get why it works until a certain point. All variable above are declared as Long so I shouldn't have an issue with having too many digits.

Let me know if you have any idea about my issue... Hopefully I missed something!

Thanks a lot!!!!

[p45cal]

Consider putting an Exit For directly after the line:
LigDelete = LigDelete + 1

After it finds a criterion, it deletes the line, but continues looking for the rest of the criteria in the cell that's replaced the cell that was there before.

Otherwise, any merged cells?

Also consider For lig = ubound to lbound step -1, then you don't have to worry about compensating for changing line numbers.

Finally, instead of copy/deleting while in the loop, set a range which gets updated in the loop like:
[somewhere earlier have the likes of: Dim StuffToDelete as range ]

and then in the loop (when instr > 0) something like:
If StuffToDelete is nothing then set StuffToDelete = ActiveSheet.Range("G" & Lig + 6) else set StuffToDelete = union(StuffToDelete,ActiveSheet.Range("G" & Lig + 6))

Finally, when all looping is complete have something like:
StuffToDelete.entirerow.copy Sheets("Deleted").Range("A1")

[Valou Choc]

It works!!!!!!!!!!!! so great!!! Thank you so much!!!!! Now I got the issue I had. I thought about everything but this...

I will take all your other comments into account as well. :-)

Have a great day ahead