Macro to run only if the data is present in Criteria Range

ok here it is

Private Sub Worksheet_Change(ByVal Target As Range)
if Range("RepCriRng") = "" then
         Msgbox "WARNING !!! Named range [RepCriRng] is empty, thus routine cannot work. Please check that Range has the correct setting and try again",vbcritical,"Name Range RepCriRng missing"
Else
         Call GenReport
End if
End Sub

Select allOpen in new window

Check if this would do it.
Gowflow

gowflow solution should work ;-)

Hi Gowflow,
It is not Working

can you post a workbook ?  as What you explained should but there is something else.

Anyway try this

Private Sub Worksheet_Change(ByVal Target As Range)
if Not Range("RepCriRng") is Nothing then
         Call GenReport
Else
         Msgbox "WARNING !!! Named range [RepCriRng] is empty, thus routine cannot work. Please check that Range has the correct setting and try again",vbcritical,"Name Range RepCriRng missing"
End if
End Sub

Select allOpen in new window

Gowflow

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("RepCriRng").Cells.Count = WorksheetFunction.CountBlank(Range("RepCriRng").Cells) Then
    MsgBox "Range is empty"
Exit Sub

End Sub

Select allOpen in new window

Thanks Gowflow and Martin,

Kindly allow me 10 minutes time.

Here better-formed code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("RepCriRng").Cells.Count = WorksheetFunction.CountBlank(Range("RepCriRng").Cells) Then
    MsgBox "Range is empty"
Exit Sub

End Sub

Select allOpen in new window

Hi Martin,
If the RepCriRng >0 then how the Sub GenReport will be called ?

I/m sorry

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("RepCriRng").Cells.Count = WorksheetFunction.CountBlank(Range("RepCriRng").Cells) Then
    MsgBox "Range is empty"
Else
    Call GenReport
End If
Exit Sub

Select allOpen in new window

End Sub

Exit Sub ?

My routine didn't work ?
I call this debugging like shooting in the dark :)
Gowflow

Hi Gowflow,
I don't know how to debug. So you have every right to say so :)

@ Martin @Gowflow
Both the Solutions are giving

Run-time Error 1004
Method Range of Object_Worksheet Failed

You don't need to debug I was talking about us trying to see the appropriate instruction without having the code or data is like shooting in the dark.

You know by the results u don't need debugging.
Gowflow

Just saw ur post.

Pls post the formula of RepCriRng
Goto Formula Menu
Choose Names
and click on RepCriRng
Copy past ehte formula here !
Gowflow

You are Right Sir !
It seems I am near to finding the reason for above issue.
Getting back here in 5 minutes.

When there is not Value, RepCriRng does not point to any range.
So I am getting an error.

RepCriRng=OFFSET(Reports!$E$1,,,MAX(IF(Reports!$E$2:$O$6<>"",ROW(Reports!$E$2:$O$6))),11)

Now When there is nothing in Rng E2:O6, Size of RepCriRng is Zero, So clicking on RepCriRng does not point to anything.

I tried
RepCriRng=OFFSET(Reports!$E$1,,,MAX(IF(Reports!$E$2:$O$6<>"",ROW(Reports!$E$2:$O$6)),1),11)

It gives only the header, but that will filter the entire data.

AFAIU if I can check whether MAX(IF(Reports!$E$1:$O$6<>"",ROW(Reports!$E$1:$O$6))) = 1, and stops the code from running if the above condition is satisfied, the it should work.

Can't u post the workbook ???
Too much here to do this mentally !!!!
Gowflow

I understand Sir. If the following does not work, I will post the workbook. It is frustrating indeed.
Here RepCriRngSize = MAX(IF(Reports!$E$1:$O$6<>"",ROW(Reports!$E$1:$O$6)))
Now if the above value is 1, then the code should not work
Otherwise the Call Routine should execute.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("RepCriRngSize").Value > 1 Then
         Call GenReport
Else
         MsgBox "WARNING !!! Named range [RepCriRng] is empty, thus routine cannot work. Please check that Range has the correct setting and try again", vbCritical, "Name Range RepCriRng missing"
End If
End Sub

Select allOpen in new window

Both the Solutions are giving

Run-time Error 1004
Method Range of Object_Worksheet Failed

Which line in my code?

If Range("RepCriRng").Cells.Count = WorksheetFunction.CountBlank(Range("RepCriRng").Cells) Then

Select allOpen in new window

RepCriRng is Blank Here.
Clicking on
Formula Menu > Names > RepCriRng does not point to anything

Let me post a workbook

ok then try this

Private Sub Worksheet_Change(ByVal Target As Range)
If MAX(IF(Reports!$E$1:$O$6<>"",ROW(Reports!$E$1:$O$6))) > 1 Then
         Call GenReport
Else
         MsgBox "WARNING !!! Named range [RepCriRng] is empty, thus routine cannot work. Please check that Range has the correct setting and try again", vbCritical, "Name Range RepCriRng missing"
End If
End Sub

Select allOpen in new window

If this doesn't work I guess goto Names in Formulas click on RepCriRngSize and in the formula insert
=MAX(IF(Reports!$E$1:$O$6<>"",ROW(Reports!$E$1:$O$6)))
Press ok to save it

and then run my original code that is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("RepCriRngSize").Value > 1 Then
         Call GenReport
Else
         MsgBox "WARNING !!! Named range [RepCriRng] is empty, thus routine cannot work. Please check that Range has the correct setting and try again", vbCritical, "Name Range RepCriRng missing"
End If
End Sub

Select allOpen in new window

Gowflow

Okay

Bug 

Screenshot

Trying the other one

Trying the Other One

Syntax Error

Wait Gowflow. I am submitting a file.

Here is a Workbook
Filter Macro Not ro Run If Criteria Range is Blank.xlsm

what should I do to reproduce the error?
gowflow

Select any Single Value in E2:H6
It will populate data
Then Delete that value

Here is a Screenshot
When I deleted the Single Value present on Cell G2, I got that error.

ok when I select a cell and choose something from the gropdown in highlight the whole sheet
if I delete the cell it highlight and delete the content of all cells
no error
gowflow

What is it supposed to do its chineese for me

Explain what you want to do what it should do and where is the problem without all this is like trying to see fortune teller
Gowflow

I dont get error in the file u posted
Gowflow

AAAHHHH
When I delete the last cell it gives and error !!!

Wait give me 1 second
Gowflow

Posting it

Now try to delete F4 from the sheet
Filter Macro Not ro Run If Criteria Range is Blank.xlsm

Hi Gowflow,
I will be back in the Morning. 3:45 am in India

I am not too sure but I thing your formula misses something Try to pste this instead of the one you have and in worksheet change just call GenReport

=OFFSET(Report!$E$1,,,MAX(IF(Report!$E$2:$H$6<>"",ROW(Report!$E$2:$H$6)),1),4)[code]

Select allOpen in new window

[/code]

Gowflow

It does work Gowflow, but it then does defeat the purpose of the filter.
If Actual database is 50000 lines, it will filter ALL the rows in that case if we adopt that formula.
That is why I tried to apply a check to see whether the MAX(IF(Report!$E$2:$H$6<>"",ROW(Report!$E$2:$H$6)),1) > 1 or not
If Yes, then the code should run otherwise it should exit the code.

The dynamic criteria range is the culprit. When there is no value in the criteria range, the dynamic criteria range is evaluated to nothing and that causes this error.
To deal with this, change your worksheet event code to this..

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub


If Not Intersect(Target, Range("E2:H6")) Is Nothing Then
    If Application.CountA(Range("E2:H6")) > 0 Then
        Call GenReport
    Else
        Call ClearDestinationRange
    End If
End If
End Sub

Select allOpen in new window

Please refer to the attached.

Filter-Macro-Not-ro-Run-If-Criteria.xlsm

Working Perfectly Subodh Ji,

One unseen Issue, however, crept up.
If user leaves a completely blank row between the Criteria Range, then the whole data is filtered
E.g., if the user enter value in F3, then the whole data is filtered as RepCriRng now contains a completely blank range.
Is there a way to force and inform the user to enter the data in rows consecutively instead of randomly ?

Regards
Kanwaljit

Okay, test this version and let me know if that works as desired.

Filter-Macro-Not-ro-Run-If-Criteria v2.xlsm

Yes, This is working Fine Sir.
If we FIRST try to Select or enter value in other than First Row, then the message is popping up.
Now if we enter data in F2 and F3 and then delete value from F3, then again entire data is populated. Such a scenario might happen practically.

Great!
I intentionally programmed it like this e.g. if you delete any value from the criteria range, the advanced filter will be executed again to return the updated data set based on the criteria left in the criteria range.

i.e. if you enter the criteria in F2 and F3, the data set would be returned based on criteria in F2 and F3 and then if you delete the criteria in F3, the updated data set would be returned based on the criteria in F2 only.
Isn't it something which you don't expect.

Definitely Yes Sir ! I Intended to work it like this. But when entire row is blank, the entire database is returned, which is not intended. I am using it to generate reports based on the User request. But that does not include exporting the entire database. So I was trying to include only those rows where some value has been asked. I understand advanced filters work on continuous rows and so include any blank rows in it. But that does hamper my purpose, as the database is nearly 100000 rows and would create a lot of issues.

If the entire criteria range is empty, no data set would be returned.
And you can't skip a row leaving a criteria range blank e.g. if you don't mention any criteria in row1, you cannot enter criteria in row2 in other words you will not be allowed to enter a criteria in any row if any of the previous row is empty.
Considering this what do you mean by "when entire row is blank, the entire database is returned"?
Maybe I am missing something here.
Can you upload a sample workbook with criteria selected and let me know what's wrong there with the returned data set?

E.g, I choose
D1 in E2, data is filtered
D2 in E3, more data is filtered
D3 in E4, one more line is filtered and added
Now I delete D1 from Cell E2, the ENTIRE data from Database Main1DB is filtered and populated in Destination Range.

Okay got it.
So what do you want in that case? Do you want to stop the user to delete the criteria from criteria row1 if there is any criteria down the rows?
Remember the first criteria row should not be empty when other criteria rows have some criteria in them.

First checkpoint
>> if the First row is blank, then the user should not be able to enter data in succeeding rows.
Second Checkpoint
>>If the data is filled beginning with First row, but then one of the previous row gets completely blank, then
--that row should be highlighted and user should be asked for some data in that row THAT blank row
or
--the data in succeeding rows should be copied and pasted one row back so that there is no blank row in between
(user is least bothered in such a solution)
or
--A VBA routine checks that whether there is a completely blank row IN Defined Named Range "RepCriRng" and if that number is greater than ZERO, the macro GenReport should not be executed and data filtered previously should remain intact.


I understand it has deviated from the original question. Should I open a new question for that ?

Regards
Kanwaljit

Why not prompt the user to not delete a criteria in a row if the rows below contain any criteria in them and not to have any empty criteria row in between?

See if the following version is something you can work with.
Filter-Macro-Not-ro-Run-If-Criteria v3.xlsm

Thanks Subodh Ji,
In between I was trying some sorting type of solution.
Definitely not elegant, but may be simple and working.
Please have a look. You are the Better person to judge.
Filter Macro Not ro Run If Criteria Range is Blank Subodh Tiwari+KSD1.xlsm
Regards
Kanwaljit

Need to Update the Clear Destination Range Starting Point from A8 to A13 in the above file

What I felt that user should not be bothered too much
--if that can be avoided and
--we can manage the things without interfering.

Code is breaking at one point.
If the filter is not active in the Range E:H
If possible kindly build some solution for that.

Yes, that approach seems to work and you should go with this if it works as desired.
Please find the attached with the tweaked codes.
Filter-Macro-Not-ro-Run-If-Criteria (1).xlsm

Yours endorsement is a big encouragement Sir. Thanks.
Kindly provide for that filter issue and please comment the code a bit so that I can understand the worksheet change routine properly
Is it possible to shorten the Sorting macro if there are multiple columns in the criteria range ?
Kanwaljit

The code is straightforward and easy to follow. If you go through it you would easily understand it.
I tweaked the codes in the attached file I uploaded in my last post.

Okay Sir Thanks !
I can see you commented out the following portion. May be redundant now !

If Target.CountLarge > 1 Then Exit Sub

Thanks a Lot Again Sir !
It was wonderful !
Regards
Kanwaljit

I commented out that line because if whole criteria range is deleted at once i.e. more than one cells get changed in the target range, the code will exit without doing anything hence it would not clear the destination range and if the criteria range is empty, the destination range should also be empty.

Glad you have got something in the end which works as desired. :)