I’m working on a VBA script in Excel that filters a range of cells and then prints the filtered rows. However, I’m encountering a runtime error and I’m not sure how to resolve it.

Date filtering : For the date filtering its supposed to only return row with dates from the previous day or the past 3 days if the current day is monday

so if today is friday the only rows visible should be from thursday and if its monday – sunday saturday and friday rows should appear (hope this makes sense)

VB script:

Set filterRange = srcSheet.Range("A1").CurrentRegion

' Apply the filter
With filterRange
    If Weekday(Date) = 2 Then ' If today is Monday
        .AutoFilter Field:=dateColumnIndex, Criteria1:=">=DATE(" & Year(Date - 3) & "," & Month(Date - 3) & "," & Day(Date - 3) & ")"
        .AutoFilter Field:=dateColumnIndex, Criteria2:="<=DATE(" & Year(Date - 1) & "," & Month(Date - 1) & "," & Day(Date - 1) & ")"
    Else ' If today is any other day
        .AutoFilter Field:=dateColumnIndex, Criteria1:="=DATE(" & Year(Date - 1) & "," & Month(Date - 1) & "," & Day(Date - 1) & ")"
    End If
    .AutoFilter Field:=statusColumnIndex, Criteria1:="LDP"
End With

' Copy the filtered data to the destination sheet
lastRow = filterRange.Resize(filterRange.Rows.Count - 1, filterRange.Columns.Count).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Row
filterRange.Resize(lastRow - filterRange.Row + 1, filterRange.Columns.Count).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Copy destSheet.Cells(1, 1)

' Clear the filters
filterRange.AutoFilter

When I run this script, I get a runtime error on this line:

lastRow = filterRange.Resize(filterRange.Rows.Count - 1, filterRange.Columns.Count).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Row

The error message is “Run-time error ‘1004’: No cells were found.”

However, when I print the number of visible rows to the debug console using

Debug.Print filterRange.Rows.SpecialCells(xlCellTypeVisible).Count,

I get 37, which suggests that the filter is working correctly and there are 37 visible rows.

But when I try to print the filtered rows using filterRange.PrintOut, it prints empty rows.
I’m not sure why I’m getting this error or why the PrintOut method isn’t printing the visible rows. Any help would be greatly appreciated!

Apologies for the bad code its my first day learning and writing VB using AI of course ;D

here is the full script for reference:

Public Sub FilterAndWriteData()
    Dim srcSheet As Worksheet
    Dim destSheet As Worksheet
    Dim lastRow As Long
    Dim filterRange As Range
    Dim dateColumnIndex As Long
    Dim statusColumnIndex As Long
    Dim visibleRows As Long
    Dim rngVisible As Range

    ' Set the source and destination worksheets
    ' Check if the "FilteredSheet" exists
    On Error Resume Next
    Set destSheet = ThisWorkbook.Worksheets("FilteredSheet")
    On Error GoTo 0

    ' If the "FilteredSheet" doesn't exist, create it
    If destSheet Is Nothing Then
        Set destSheet = ThisWorkbook.Worksheets.Add
        destSheet.Name = "FilteredSheet"
    End If

    ' Set the source worksheet
    Set srcSheet = ThisWorkbook.Worksheets("Sheet1")

    ' Find the column indices for date and status columns
    dateColumnIndex = srcSheet.Rows(1).Find(What:="Load Date", LookIn:=xlValues, LookAt:=xlWhole).Column
    statusColumnIndex = srcSheet.Rows(1).Find(What:="Final Status", LookIn:=xlValues, LookAt:=xlWhole).Column

    ' Clear the destination sheet
    destSheet.UsedRange.Clear

    ' Set the filter range
    Set filterRange = srcSheet.Range("A1").CurrentRegion

    ' Apply the filter
    With filterRange
        If Weekday(Date) = 2 Then ' If today is Monday
            .AutoFilter Field:=dateColumnIndex, Criteria1:=">=DATE(" & Year(Date - 3) & "," & Month(Date - 3) & "," & Day(Date - 3) & ")"
            .AutoFilter Field:=dateColumnIndex, Criteria2:="<=DATE(" & Year(Date - 1) & "," & Month(Date - 1) & "," & Day(Date - 1) & ")"
        Else ' If today is any other day
            .AutoFilter Field:=dateColumnIndex, Criteria1:="=DATE(" & Year(Date - 1) & "," & Month(Date - 1) & "," & Day(Date - 1) & ")"
        End If
        .AutoFilter Field:=statusColumnIndex, Criteria1:="LDP"
    End With

    visibleRows = filterRange.Rows.SpecialCells(xlCellTypeVisible).Count - 1
    Debug.Print "The size of the filtered data is: "; visibleRows

    ' Get the visible cells in filterRange
On Error Resume Next
Set rngVisible = filterRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
' Check if any visible cells were found
If Not rngVisible Is Nothing Then
    ' Print the visible cells
    rngVisible.PrintOut
Else
    MsgBox "No visible cells were found in the filter range."
End If

    ' Copy the filtered data to the destination sheet
    'lastRow = filterRange.Resize(filterRange.Rows.Count - 1, filterRange.Columns.Count).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Row
    'filterRange.Resize(lastRow - filterRange.Row + 1, filterRange.Columns.Count).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Copy destSheet.Cells(1, 1)

    ' Clear the filters
    ' filterRange.AutoFilter
End Sub

I tried to filter using

With filterRange
        If Weekday(Date) = 2 Then ' If today is Monday
            .AutoFilter Field:=dateColumnIndex, Criteria1:=">=DATE(" & Year(Date - 3) & "," & Month(Date - 3) & "," & Day(Date - 3) & ")"
            .AutoFilter Field:=dateColumnIndex, Criteria2:="<=DATE(" & Year(Date - 1) & "," & Month(Date - 1) & "," & Day(Date - 1) & ")"
        Else ' If today is any other day
            .AutoFilter Field:=dateColumnIndex, Criteria1:="=DATE(" & Year(Date - 1) & "," & Month(Date - 1) & "," & Day(Date - 1) & ")"
        End If
        .AutoFilter Field:=statusColumnIndex, Criteria1:="LDP"
    End With

to filter by the columns and paste the results on a different sheet in the same workbook this is the overall aim of the script