VBA No cells were found

sarantip

New Member
Joined
Aug 12, 2017
Messages
3
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
quick fix
Code:
If myRng.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
 visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
End If
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.

Code:
'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
 
Upvote 0
quick fix
Code:
If myRng.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
  visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
 End If
You could modify it to this.
Code:
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.
 
Last edited:
Upvote 0
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:

Code:
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:

Code:
[I]visibleTotal = Excel.WorksheetFunction.Subtotal([/I]xlSubtotalSum + xlSubtotalIgnoreHiddenCells[I], myRng)[/I]
 
Upvote 0
Assuming column A determines the extent of your data, try making this change only in your original code: Remove the red text
Rich (BB code):
Set myRng = Cells(2, 17).Resize(Lrow - 1, 1)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top