VBA No cells were found

[sarantip]

Hello,

I tries to run a macro and a message pops up "No cells were found".
The Macro runs fine until the variable dy1 is 5 (Meaning it is day 5 of the month).
Some days are missing as there are weekends were no data are available.
The filter returns nothing (no visible data) as there isn't day 5 in the dataset.
Then the variable visibleTotal cannot be executed. An error pops up: no cells were found.
Can I please get some help?

Sub CountLines()
Dim dy1 As Variant
Dim hour1 As Variant
Dim mth1 As Variant
Dim myRng As Range
Dim Lrow As Long
Dim visibleTotal As Long




'Find the last Row
Lrow = Cells(Rows.Count, 1).End(xlUp).Row


Application.ScreenUpdating = False


For n = 2 To 25 'no of columns for hours
For m = 3 To 33 'rows of days

dy1 = Sheets("Lines").Cells(m, 1).Value
hour1 = Sheets("Lines").Cells(2, n).Value
mth1 = Sheets("Database").Cells(1, 19).Text


Sheets("Database").Select


ActiveSheet.Range("a1:s1").AutoFilter field:=13, Criteria1:=mth1
ActiveSheet.Range("a1:s1").AutoFilter field:=16, Criteria1:=dy1
ActiveSheet.Range("a1:s1").AutoFilter field:=14, Criteria1:=hour1



Cells(2, 17).Select
Set myRng = Cells(2, 17).Resize(Lrow - 1, 1)
visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))


'Paste VisibleTotal in the lines sheet
'Added screenupdating
Application.ScreenUpdating = True
Sheets("Lines").Cells(m, n).Value = visibleTotal
Application.ScreenUpdating = False


'Deactivate AutoFilter
Sheets("Database").Select
ActiveSheet.AutoFilterMode = False


Next 'next row
Next 'next Column


End Sub

 

[JLGWhiz]

quick fix

If myRng.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
 visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
End If

 

[sarantip]

I have tried the If statement but still there is a problem. When dy1 is 5 and I try to execute the If myRng.specialcells (xlCellTypeVisible).Rows.Count > 1 Then a message pops up; No cells were found. Any ideas?

Cells(2, 17).Select
Set myRng = Cells(2, 17).Resize(Lrow - 1, 1)
If myRng.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
End If

 

[Rick Rothstein]

On Error Resume Next
visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
On Error GoTo 0

Try wrapping the above line with the error trapping statements shown above in red.

 

[LockeGarmin]

Here is another option that would allow you to not worry about error trapping, though it may be a little hard to remember what it's doing when you are reading the code later hence the comment above the Subtotal line.

'Subtotal Sum Function, Ignore Hidden Cells
[COLOR=#333333][I]visibleTotal = Excel.WorksheetFunction.Subtotal(109, myRng)[/I][/COLOR]

It can do other functions such as averages, max, min etc. I included the documentation below.
SUBTOTAL function - Office Support

 

[JLGWhiz]

quick fix

If myRng.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
  visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
 End If

You could modify it to this.

If Sheets("Database").Cells(2, 17) <> "" Then
  visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
 End If

On second thought, that probably won't work either. I think Rick probably has the best answer.

 

[Rick Rothstein]

I think Rick probably has the best answer.

I kind of like LockeGarmin's solution myself.

 

[sarantip]

Thank you all. Problem solved!

 

[LockeGarmin]

I realized earlier this evening that if you find yourself using a few subtotal functions in VBA rather than putting comments above them you could include an enum in a new module (perhaps named "xlEnums") like this:

Public Enum xlSubtotal
  xlSubtotalAverage = 1
  xlSubtotalCount
  xlSubtotalCountA
  xlSubtotalMax
  xlSubtotalMin
  xlSubtotalProduct
  xlSubtotalStDev
  xlSubtotalStDevP
  xlSubtotalSum
  xlSubtotalVar
  xlSubtotalVarP
  xlSubtotalIgnoreHiddenCells = 100
End Enum

And then use subtotal like this:

[I]visibleTotal = Excel.WorksheetFunction.Subtotal([/I]xlSubtotalSum + xlSubtotalIgnoreHiddenCells[I], myRng)[/I]

 

[Peter_SSs]

Assuming column A determines the extent of your data, try making this change only in your original code: Remove the red text

Set myRng = Cells(2, 17).Resize(Lrow - 1, 1)