Problem Between Test and Physical Run

[bmwguy525]

Hey guys, I have some pretty sophomoric code and I'm looking for help as to why I can run it with no problem on a test page with select data, but not a full range.

The test page has exact data copied from the full page for 100 rows, and runs without problem. The full page is upward of 43,000 lines and a couple seconds after I run the code, VBA gives me an error and stops.


Here's the code:Sub DeleteErrors()
Dim i As Long
Dim iLastRow As Long
Dim x As Long
Dim xLastRow As Long

With ActiveSheet

Application.ScreenUpdating = False

Columns("B:B").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
.Rows(i + 1).Insert
.Cells(i + 1, 1).Value = "item_id"
Next i

xLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For x = 1 To xLastRow
If Cells(x, "A").Text = "item_id" Then
Cells(x, "A").EntireRow.Hidden = True
End If
Next x

Application.ScreenUpdating = True

End With
End Sub

The code itself is probably pretty shoddy (I have no idea what I'm doing to be quite honest :(), but it works fine on the test and not on the actual page.

And I know that I'm going about this the completely wrong way but this is the code I've been able to figure out so far. What I'm trying to do is match a list from Sheet1 ColumnA to Sheet2 ColumnA, both columns of which have some cells that don't have matching values. I need to narrow down the list to only the matching items, and then pull the value from Sheet2 ColumnB into the corresponding cell in Sheet1 ColumnB for the matching item in ColumnA. :think:

I don't know if this is making any sense, but my lack of experience in programming is making this pretty difficult for me. Any help I could get is greatly appreciated!

[Bob Phillips]

Sub DeleteErrors()
Dim i As Long
Dim iLastRow As Long
Dim x As Long
Dim xLastRow As Long

With ActiveSheet

Application.ScreenUpdating = False

.Columns("B:B").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
.Rows(i + 1).Insert
.Cells(i + 1, 1).Value = "item_id"
Next i

xLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For x = 1 To xLastRow
.Rows(x).Hidden = .Cells(x, "A").Text = "item_id"
Next x

Application.ScreenUpdating = True

End With
End Sub

[Norie]

Well the main problem I can see is that you haven't actually qualified all your references.

You've done it here.

.Rows(i + 1).Insert

But not here.

Columns("B:B").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
That little dot (qualifier) highlighted can be all important.

Without it VBA will assume you are referring to the active sheet.