We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x
Private

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

High Priority
65 Views
Last Modified: 2020-05-09
Hi Experts,
I am using the following macro to apply advance filter and fetch the data.
Sometimes the User Delete All the entries from RepCriRng i,e., Criteria Range and then it pops up a message immediately as the code is being called via Worksheet_Change Event.
I request some routine which can check that if the RepCriRng is Empty then the Code does not run and it runs only when some data is present in the Fields covered by RepCriRng

RepCriRng is a named ranges created using the following
=OFFSET(Reports!$E$1,,,MAX(IF(Reports!$E$2:$P$7<>"",ROW(Reports!$E$2:$P$7))),11)

Sub GenReport()
'Dim WS As Worksheet

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With
    
'---> Unprotect Sheet
WS.Unprotect "password"

'--->Filter the range and Autofit the Entire Sheet
    
    Range("Main1DB").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("RepCriRng"), CopyToRange:=Range("RepDesRng"), Unique:=False
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("E2").Select
    Application.Calculate

'---> Protect Worksheet
WS.Protect Password:="password", DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

'--->Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

Open in new window

Private Sub Worksheet_Change(ByVal Target As Range)
Call GenReport
End Sub

Open in new window

Comment
Watch Question

gowflowPartner
CERTIFIED EXPERT

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

Open in new window



Check if this would do it.
Gowflow
Louis LIETAERSystem Infrastructure Architect
CERTIFIED EXPERT

Commented:
gowflow solution should work ;-)
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Hi Gowflow,
It is not Working
gowflowPartner
CERTIFIED EXPERT

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

Open in new window


Gowflow
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:

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

Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Thanks Gowflow and Martin,

Kindly allow me 10 minutes time.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Open in new window

Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Hi Martin,
If the RepCriRng >0 then how the Sub GenReport will be called ?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Open in new window


End Sub
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Exit Sub ?
gowflowPartner
CERTIFIED EXPERT

Commented:
My routine didn't work ?
I call this debugging like shooting in the dark :)
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

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

Author

Commented:
@ Martin @Gowflow
Both the Solutions are giving

Run-time Error 1004
Method Range of Object_Worksheet Failed


gowflowPartner
CERTIFIED EXPERT

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
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
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
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.
Kanwaljit Singh DhunnaSelf Employed

Author

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

Author

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

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
Can't u post the workbook ???
Too much here to do this mentally !!!!
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

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


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Both the Solutions are giving

Run-time Error 1004
Method Range of Object_Worksheet Failed
Which line in my code?
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
If Range("RepCriRng").Cells.Count = WorksheetFunction.CountBlank(Range("RepCriRng").Cells) Then
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
RepCriRng is Blank Here.
Clicking on
Formula Menu > Names > RepCriRng does not point to anything
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Let me post a workbook
gowflowPartner
CERTIFIED EXPERT

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

Open 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

Open in new window

Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Okay
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Bug 
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Screenshot
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Trying the other one
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Trying the Other One
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Syntax Error
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Wait Gowflow. I am submitting a file.
gowflowPartner
CERTIFIED EXPERT

Commented:
what should I do to reproduce the error?
gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Select any Single Value in E2:H6
It will populate data
Then Delete that value
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Here is a Screenshot
When I deleted the Single Value present on Cell G2, I got that error.
gowflowPartner
CERTIFIED EXPERT

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
I dont get error in the file u posted
Gowflow
gowflowPartner
CERTIFIED EXPERT

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

Wait give me 1 second
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Posting it
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Now try to delete F4 from the sheet
Filter Macro Not ro Run If Criteria Range is Blank.xlsm
Kanwaljit Singh DhunnaSelf Employed

Author

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

Commented:
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]

Open in new window

[/code]

Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
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.

Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

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


Please refer to the attached.

Filter-Macro-Not-ro-Run-If-Criteria.xlsm
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
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
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

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

Filter-Macro-Not-ro-Run-If-Criteria v2.xlsm
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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?
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
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.

Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
Kanwaljit Singh DhunnaSelf Employed

Author

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

Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
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
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Need to Update the Clear Destination Range Starting Point from A8 to A13 in the above file
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
What I felt that user should not be bothered too much
--if that can be avoided and
--we can manage the things without interfering.
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
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
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
See if the issue you reported is resolved in this version.

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
Kanwaljit Singh DhunnaSelf Employed

Author

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

Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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. :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.