Code not working as expected

[Gazza2]

Hope this is the right forum.

I have a worksheet that i use to import a report to and then i run the following code to remove the unwanted lines :

Dim x As Integer
Dim LastRow As Long
LastRow = Range("a65535").End(xlUp).Row


Range(Cells(LastRow, 11), (Cells(1, 11))).FormulaR1C1 = _
"=IF(VLOOKUP(RC[-10],'(sheet somewhereelse)sheet1'!R2C1:R22C1,1,false),rc[-10],"""")"

For x = 1 To LastRow Step 1

If Cells(x, 11).Text = "#N/A" Then Cells(x, 1).EntireRow.Delete

Next x

The code works as i want it too (sort of) but i have to run it 3-6 times to get it too delete all the unwanted lines.

Basically what i do is :

Import the report which has hundreds of lines, Column A has an ID to identify it into Workbook 1

I have another Workbook (Workbook2) stored on a server with the ID no and some other information for later use.

I use the first part of this code(the formula) to check if the ID in Column A of Workbook1 exists in column A of Workbook2. (this works OK and gives an #N/A if the ID doesnt exist).

The second part of the code is supposed to go through each row of column K in Workbook1 and if there is an #N/A in the box then it deletes the entire row. (Now this kind of works but it will only delete a few lines then i have to run it again maybe 3 or 4 times to get it to clear all the lines).

Now obviously i have done something wrong but i cant figure out what it is so any help will be much appreciated.

Thanks

Gareth

 

[HaHoBe]

Hi, Gareth,

deleting rows should start at the bottom and work up to the top. If you work down any deletion will cause the counter to miss a line for the check.

Another pit may be the dimensions as you use long for the last row but an Integer for the counter, passing 32.767 rows will throw up a run-time error.

Dim lngRow As Long
Dim lngLastRow As Long

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range(Cells(lngLastRow, 11), (Cells(1, 11))).FormulaR1C1 = _
  "=IF(VLOOKUP(RC[-10],'(sheet somewhereelse)sheet1'!R2C1:R22C1,1,false),rc[-10],"""")"

Application.ScreenUpdating = False
For lngRow = lngLastRow To 1 Step -1
  If Cells(lngRow, 11).Text = "#N/A" Then Rows(lngRow).Delete
Next lngRow
Application.ScreenUpdating = True

Have you considered entering a Label in Row 1 and use the autofilter instead a loop?

HTH
Holger