Know when filter returns no rows

[Lucas Soler]

I get an error in my macro when I use a filter and it returns no rows. I
can't figure out how to determine if no rows are returned from the filter.
Any ideas?

Thanks!

 

[Rod Gill]

If a filter returns no tasks, a test can be:

SelectBeginning 'Make sure cursor is on first row
If ActiveCell.Task is nothing Then
MsgBox "No tasks"
else
MsgBox "Task visible"
Endif
--

Rod Gill
Project MVP
Visit www.msproject-systems.com for Project Companion Tools and more

NEW!! Project VBA Book now in stock, for details visit:
www.projectvbabook.com

 

[John]

I get an error in my macro when I use a filter and it returns no rows. I
can't figure out how to determine if no rows are returned from the filter.
Any ideas?

Thanks!

Lucas,
One way to address this condition is as follows:
On Error Resume Next
'loop to process filtered tasks
For Each t in Active.Selection.Tasks
If Err>0 Then
[alert user with a MsgBox statement]
End Sub
End If
'clear the error so new errors will be flagged
On Error GoTo 0
[code if there are tasks to process]
Next t
[rest of macro]

Hope this helps.
John
Project MVP

 

[Lucas Soler]

This worked great, thanks!

If a filter returns no tasks, a test can be:

SelectBeginning 'Make sure cursor is on first row
If ActiveCell.Task is nothing Then
MsgBox "No tasks"
else
MsgBox "Task visible"
Endif
--

Rod Gill
Project MVP
Visit www.msproject-systems.com for Project Companion Tools and more

NEW!! Project VBA Book now in stock, for details visit:
www.projectvbabook.com

 

[Lucas Soler]

John,
This didn't work and gave me an error "object doesn't exist", and I assume
it's because there are no tasks to select.

I tried Rod Gill's suggestion, and it worked great. Thanks for taking a
look at this, John.

Cheers

I get an error in my macro when I use a filter and it returns no rows. I
can't figure out how to determine if no rows are returned from the filter.
Any ideas?

Thanks!

Lucas,
One way to address this condition is as follows:
On Error Resume Next
'loop to process filtered tasks
For Each t in Active.Selection.Tasks
If Err>0 Then
[alert user with a MsgBox statement]
End Sub
End If
'clear the error so new errors will be flagged
On Error GoTo 0
[code if there are tasks to process]
Next t
[rest of macro]

Hope this helps.
John
Project MVP

 

[John]

John,
This didn't work and gave me an error "object doesn't exist", and I assume
it's because there are no tasks to select.

I tried Rod Gill's suggestion, and it worked great. Thanks for taking a
look at this, John.

Lucas,
My bad. That's what happens when I don't take the time to actually test
code that I post. I just typed it in and hit "post". Unfortunately my
code has two errors. The correct code is as follows:
On Error Resume Next
'loop to process filtered tasks
For Each t in ActiveSelection.Tasks 'first error: corrected
If Err > 0 Then
[alert user with MsgBox statement]
Exit Sub 'second error: corrected
End if
'clear the error so new errors will be flagged
On Error GoTo 0
[code if there are tasks to process]
Next t
[rest of macro]

Sorry for the screw-up.

John
Project MVP

Cheers

I get an error in my macro when I use a filter and it returns no rows. I
can't figure out how to determine if no rows are returned from the
filter.
Any ideas?

Thanks!

Lucas,
One way to address this condition is as follows:
On Error Resume Next
'loop to process filtered tasks
For Each t in Active.Selection.Tasks
If Err>0 Then
[alert user with a MsgBox statement]
End Sub
End If
'clear the error so new errors will be flagged
On Error GoTo 0
[code if there are tasks to process]
Next t
[rest of macro]

Hope this helps.
John
Project MVP