trying to understand why my code is crashing

[termeric]

Hello, I've been using this code to delete everything on a spreadsheet that doesnt match a customer number in a column. it's been workign fine up until today, when i am suddently getting a type mismatch error. I am looping through a list of items on a spreadsheet and pulling the values for the tab to inspect, the ID type to use, the column that the ID is in, and the row where the data starts after a header.

It appears to crash after all the rows that do not match the ID have been deleted, and sometimes crashes on the first tab (NavEOB) and other times crashed on the second tab (File Frequency) but never makes it further than that. Is there somethign i need to chance to make this work consistently? or a better way to write this all together?



This is where its crashing

Sub Isolation()

Dim Tabs As String, Filter As String, col As String, row As Long
Dim lr As Long, r As Long

lr = Sheets("Links").UsedRange.Rows.Count

Sheets("Control").Range("A18:H40").Delete

Sheets("Control").Range("C19").Value = "Tab"
Sheets("Control").Range("F19").Value = "Result"
Sheets("Control").Range("A19:f19").Font.Bold = True

For r = 2 To lr

    it = r + 18

    Tabs = Sheets("Links").Range("B" & r).Value
    Filter = Sheets("Links").Range("c" & r).Value
    col = Sheets("Links").Range("f" & r).Value
    row = Sheets("Links").Range("g" & r).Value

    x = Del_Fast(Tabs, Filter, col, row)
    x = report(Tabs, Filter, col, row)

Next r


Sheets("Control").Select
End Sub


Function Del_Fast(t As String, f As String, c As String, r As Long)

    'cuts the NAVEOB list down to the necessary records
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long, id As Long
    
    If f = "PH Number" Then
        id = Sheets("Control").Range("PH").Value
    Else
        id = Sheets("Control").Range("PSU").Value
    End If
    
    
  Sheets(t).Select
 
  nc = Sheets(t).Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Sheets(t).Range(c & r, Sheets(t).Range(c & Rows.Count).End(xlUp)).Value
  
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If Not a(i, 1) Like "*" & id Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    'Application.ScreenUpdating = False
    With Sheets(t).Range("A" & r).Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Delete
    End With
    

    
    
    'Application.ScreenUpdating = True
  End If
End Function

[\code]

 

[DanteAmor]

ReDim b(1 To UBound(a), 1 To 1)

The problem is because in the array 'a' you have a record or there are no records.

If 'a' is empty, I guess it shouldn't do the process.

If 'a' has a record then the for must go from 1 to 1.
First you need to get the last row with data and determine if it is less than 2.

O change this

a = Sheets(t).Range(c & r, Sheets(t).Range(c & Rows.Count).End(xlUp)).Value

For this:

a = Sheets(t).Range(c & r, Sheets(t).Range(c & Rows.Count).End(xlUp)(2)).Value

So that you always have more than one row, and in the process it discards the empty cell.

 

[termeric]

a = Sheets(t).Range(c & r, Sheets(t).Range(c & Rows.Count).End(xlUp)(2)).Value

So that you always have more than one row, and in the process it discards the empty cell.

Thanks @DanteAmor that appears to have fixed the problem, however, now i'm crashing in a different spot.

on this sheet, nc = 17, and at the moment r = 2. i do have data in the column.

any thouhgts on what i might need to fix here?

 

[DanteAmor]

From what I can see on your image you have a table. You are trying to sort the table by a column that is outside the table.

Either you put everything inside the table or better convert the table to a range data.

 

[Peter_SSs]

First, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

As Dante has noted, you are dealing with a formal table in this case. That sort & delete code looks like it could well be something I have posted in the past and it was not written for a formal table (ListObject).

However, I would not convert your table to a normal range just to accommodate this code. Instead, I would suggest checking the following setting:

File - Options - Proofing - AutoCorreect Options... - AutoFormat As You Type tab - Include new rows and columns in table, ensure that checkbox is ticked - OK

.. and modify this part of the code slightly.

  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A2").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlYes
      .Resize(k).EntireRow.Delete
      .Columns(nc).EntireColumn.Delete
    End With

If