Function works for first 2 task but does nothing on the 3rd

Question

This is an interesting one.

I have written a function that takes a list of data and separates the list into 2 worksheets. The first contains less than 90 days prior to invoice date and the 2nd contains greater than 90 days prior to invoice date. This function works as expected for the first and second list but for the 3rd list (same format as the other two) it fails to do anything.

The workbook has 7 pages.

Page 2 is the 1st list. Page 3 is the greater than 90 sheet for the 1st list.

Page 4 is the 2nd list. Page 5 is the greater than 90 sheet for the 2nd list.

Page 6 is the 3rd list. Page 7 is the greater than 90 sheet for the 3rd list.

For whatever reason the function works without fail until it is time to work page 6 and 7.

Here is my call to the function:

Call split90(2, first_of_month, inv_wb)
Call split90(4, first_of_month, inv_wb)
Call split90(6, first_of_month, inv_wb)

the 1st argument is used to define what page is being worked on. The 2nd argument is use to pass the first date for the month. The 3rd argument is used to pass the workbook where the function will be manipulating the list.

Here is the function:

Function split90(start_sheet, first_of_month, inv_wb)

    Dim rng_wk As Range
    Dim last_row As Integer
    Dim stop_row As Integer

    Debug.Print ("Before last_row: " & inv_wb.Worksheets(start_sheet).Cells(Rows.Count, "C").End(xlUp).Row)
    last_row = inv_wb.Worksheets(start_sheet).Cells(Rows.Count, "C").End(xlUp).Row
    Debug.Print ("After last_row: " & last_row)
    Set rng_wk = inv_wb.Worksheets(start_sheet).Range("C2:C" & last_row)

    For Each cell In rng_wk
        If Not IsEmpty(cell.Value) Then
            If CDate(cell.Value) < CDate(first_of_month) - 90 And CDate(cell.Value) <> CDate("1/1/0001") Then
                stop_row = cell.Row
                Exit For
            End If
        End If
    Next cell

    Set rng_wk = inv_wb.Worksheets(start_sheet).Range("A" & stop_row & ":H" & last_row)
    rng_wk.Cut Destination:=inv_wb.Worksheets(start_sheet + 1).Range("A2")

End Function

The above works as expected for list 1 and 2 but when I pass the number 6 for list 3 it just fails. Nothing. No error. Just nothing. All of my testing with Debug.Print() had shown me that for some reason last_row is not getting a value assigned to it when start_sheet is the integer 6.

From the above Debug.Print I expect to see the last row of the list printed twice for each call to the function. Instead what I see is the last row printed twice for the first 2 list and only once for the 2nd list. It makes no sense to me.

Here are the results of the print.

Before last_row: 20892
After last_row: 20892
Before last_row: 3098
After last_row: 3098
Before last_row: 35738

As you can see for some reason the variable last_row is not getting assigned a value for the last group... But it is able to see the number before assigning to last_row.

Answer 1

Dim last_row As Integer

should be

Dim last_row As Long

The limit for Integer is ~32,000, so for large sets of data last_row will overflow and raise a run-time error.