Thank you Randy for doing these basic training. I really hope you could do a 1. do while loop 2. Find and find next aldo with multiple columns Hope you could help
Hi and thanks. Yes for sure I will be doing these in the Basic VBA Series. (You had requested the Find and find next with multiple criteria before and i have already written it down in my notes) Thanks so much for your suggestions.
Hi Randy! First of all thank you very much for all your videos on VBA. I have learnt so much and really appreciate your channel. With regards to this video, I managed to recreate every filter and got them to work, apart from one. The date Filter. For some reason, the filter (when applied) removed all my dates in the column and left no lines. I checked everything close to a thousand times and after trying a few different options to filter between, I realised something... I have my PC set to UK Regional Settings so my dates are dd/mm/yyyy. For some reason, when filtering dates in Excel, it wanted me to use the US settings within my code... so got it to work with mm/dd/yyyy. How can this be fixed on any other regional settings?
HI and thanks for your comment Jack. Dates are no doubt very tricky. The key is to make sure ALL of your date formats are the same. That means your regional computer settings, your excel data settings, any excel default date formats, etc. No doubt the issue is incorrect formats, so once all the date formats are exactly the same it should work well. When filtering by dates its always best to use the date code #, for example I would use ">45325" which would mean greater than today (February 3rd) when using these numbers excel will be sure to get the correct dates. You can see these numbers when you enter a date then change the format to General I hope this helps and thanks so much.
hi Randy again thanks for the great content. I had same problem with Aust Dates, still can't get any of the date sample to work, sorry just one 15/01/2023 - None of the others worked. tried manual entry checked system settings no date formats in format cells. Odd to say the least. I might try a fresh workbook tomorrow and redo see if that helps
Hi and thanks, you will want to make sure to set the date formats in your computer, excel and in the code to all the same format such as Format(YourDate,"mm/dd/yyyy") I hope this helps and thanks so much.
HI @messaoudpolitique9208 thanks so much for your comment. You can sign up for an annual subscription as a member here: www.patreon.com/ExcelForFreelancers I hope this helps and thanks so much.
Quick question on using "OR" criteria. You had both criteria in the same column becuase the tested values where of the same variable. Suppose you had the Fruits and Price as the column headers and wanted to use an OR such as containing apple or anything ess than 5 on the price. Can this be done since the layout would have to include a blank criteria but then it is some what of a hodge-podge on syntax since it has both columns implying "and." So maybe this cannot be done?
HI Malcom, thanks so much. You would have to expand both the the columns and the rows of your criteria to include both OR and AND options. Yes it can get a bit tricky and for this basic lesson, I did not want to confusing people. I hope this helps and thanks so much.
Hi and thanks so much. When using AutoFilter, and want to filter based on more than one condition you can have multiple fields such as FIeld=1 and Field=2, This allows you to filter based on more than 1 criteria. I hope this helps and thanks so much.
When I filter the Data, on the sheet1 there is 1 transaction was filtered, but when copy to sheet 2, there are 4 or more transactions. And even there are rows os "#N/A"' after the filtered rows. Could you help me with this problem? Thank you so much
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Hi and and thanks you can use the last row of data using .Range("A99999").end (xlup).row then run the advanced filter based on the full set of data. The blank row will be excluded I hope this helps and thanks so much.
Hi and thanks very much. I do combine strings often in Excel. Please check any of my email templates as i combine variables with the subject or email body. I hope this helps and thanks so much.
Hmm good question. I have many mentors. From a marketing standpoint, Leila Gharani is pretty amazing, from a personal development, Jim Rohn was a pioneer and someone who inspired me to work online and create courses was Brendon Burchard. Thanks for the great question.
Seriously?? Leila.... She has been very helpful to me with Excel. But??? You're doing amazing things with Excel that I haven't seen Leila do yet.. @@ExcelForFreelancers
Yes, sure her and I, are very different in her approach to Excel, but she has 2.5 MILLION subscribers. That is insane! Of course her material is much more for a broader audience than mine but still, truly amazing, and her marketing and video editing are fantastic. Thanks again for your continued support.
@ExcelForFreelancers You'll get to a million subscribers soon go beyond.. Keep your consistency up.. Leila has worked for over 7 years on TH-cam and sure has good marketing. Learn from her and thrive Sir. All the best Randy
Randy I've written this code given below, is there anything wrong in this code? Because, when I run this code, nothing happens. No error is shown also. I can't understand where my error lies? Option Explicit Sub AdvancedFilter() If Sheet1.FilterMode = True Then Sheet1.ShowAllData
Sheet1.Range("A1").CurrentRegion.AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Sheet1.Range("F1:J10"), _ CopyToRange:=Sheet1.Range("L1:O1"), _ Unique:=False End Sub =============== I have a demo data ([Date], [Fruit], [Quantity], [Region]) starting from cell "A1" to cell "D51". My Criteria Range ([Date] , [Date] , [Fruit], [Quantity], [Region]) is from cell "F1" to cell "J10". And my Output Range ([Date], [Fruit], [Quantity], [Region]) is from cell "L1" to cell "O1". Please Randy tell me, where is my flaw here?
Sorry Randy, It was my mistake. Finally I'm able to find out my mistake. Actually what happened, I wrote the date in "DD/MM/YYY" format in the Criteria Range. In our country, we write Date in "DD/MM/YYY" format. The moment I change the Date Format from "DD/MM/YYY" to "MM/DD/YYY", it gives me correct output.
Hi and thanks for your comment Upon reviewing the code, here are some potential areas where the problem might lie: 1. Criteria Range Issues The criteria range (Sheet1.Range("F1:J10")) must follow very specific rules for advanced filters. The criteria headers (F1:J1) must match exactly the headers in the source data range (A1:D1), without any spelling differences or trailing spaces. It's also important that the criteria provided within the range (F2:J10) are valid and aligned with the data. 2. Headers in CopyTo Range The CopyToRange (Sheet1.Range("L1:O1")) should have headers that match exactly with those in the source data (A1:D1). If the headers in L1:O1 don't match, the filter will not copy anything, and no error will occur either. 3. AdvancedFilter Requirements Ensure that the output (CopyToRange) is not overlapping with the source range (A1:D51). Since the ranges do not overlap here, it should be fine. Make sure there are no blank headers in the CopyToRange (L1:O1). Blank headers can also cause the filter to silently fail. Possible Solution Verify that your criteria range (F1:J10) has valid criteria that match the headers in A1:D1 and that they are formatted properly. Ensure that L1:O1 matches the headers exactly from A1:D1. I suggest adding some debug messages to check the state of the filter and to confirm that the regions are selected as expected. Here's an updated version of the code with debug messages: Sub AdvancedFilter() ' Check if Sheet1 is available If Sheet1 Is Nothing Then MsgBox "Sheet1 is not found. Please check the sheet name.", vbCritical Exit Sub End If ' Clear any existing filter If Sheet1.FilterMode = True Then Sheet1.ShowAllData End If ' Clear contents of output range Sheet1.Range("L1").CurrentRegion.Offset(1, 0).ClearContents ' Debug message to check ranges MsgBox "Copying from A1:D51 to L1:O1 with criteria in F1:J10." ' Apply the advanced filter On Error Resume Next Sheet1.Range("A1:D51").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Sheet1.Range("F1:J10"), _ CopyToRange:=Sheet1.Range("L1:O1"), _ Unique:=False ' Error handler in case the filter fails If Err.Number 0 Then MsgBox "Error in applying filter: " & Err.Description, vbCritical Err.Clear End If On Error GoTo 0 End Sub I hope this helps and thanks so much.
@@ExcelForFreelancers Thank you Randy for guiding me. You are truly a gem. Once I complete your basic VBA training, I will start following your real life Projects. Randy if you don't mind, would you kindly give me your WhatsApp number? From now onwards, I want to stay connected with you.
Hi and thanks very much. For questions, please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
🎄 𝗦𝗮𝘃𝗶𝗻𝗴𝘀 𝗔𝘀 𝗕𝗿𝗶𝗴𝗵𝘁 𝗔𝘀 𝗖𝗵𝗿𝗶𝘀𝘁𝗺𝗮𝘀 𝗟𝗶𝗴𝗵𝘁𝘀 - 𝗨𝗽 𝗧𝗼 𝟱𝟱% 𝗪𝗮𝗶𝘁𝗶𝗻𝗴 𝗙𝗼𝗿 𝗬𝗼𝘂! 👉 www.excelforfreelancers.com/XmasSale2024_YTPinnedComm
⚡ 𝗦𝗜𝗠𝗣𝗟𝗜𝗙𝗬 𝗖𝗢𝗠𝗣𝗟𝗘𝗫 𝗘𝗫𝗖𝗘𝗟 𝗧𝗔𝗦𝗞𝗦 𝗜𝗡𝗦𝗧𝗔𝗡𝗧𝗟𝗬 𝗪𝗜𝗧𝗛 𝗘𝗫𝗖𝗘𝗟 𝗔𝗜 𝗧𝗢𝗢𝗟𝗣𝗔𝗖𝗞 𝗔𝗗𝗗-𝗜𝗡 👉 www.excelforfreelancers.com/ai-toolpack
Once again Randy, thank you so much for this weekly VBA training. I wish you a Happy and Excellent New Year.
For sure, you are very welcome Carlos, and Happy New Year you to you and your family as well
Thank you Randy for doing these basic training.
I really hope you could do a
1. do while loop
2. Find and find next aldo with multiple columns
Hope you could help
Hi and thanks. Yes for sure I will be doing these in the Basic VBA Series. (You had requested the Find and find next with multiple criteria before and i have already written it down in my notes)
Thanks so much for your suggestions.
Thank you Randy for this amazing video.
For sure, you are very welcome and I am happy to help adn share :)
for ever you are the best in excel , this my opinion
Thank you so very much, I really appreciate that
I appreciate that, thank you and waiting to have new best videos like these.....
For sure, you are very welcome and thanks so much. Yes every Saturday is a new training for VBA Beginners. I hope this helps and thanks so much.
Happy New Year Randy THNX For Sharing
For sure, you are very welcome Henk and Happy New Year to you and your family. Thank you for your Likes, Shares & Comments. It really helps.
Thanks a lot we need this type of videos, all that matters with vba
Great and thanks so much Khalid. I am happy to help and share
Hi Randy thanks for sharing
For sure, you are very welcome Tobaye, and thanks for your continued support over many years. Happy New Year
Hi Randy! First of all thank you very much for all your videos on VBA. I have learnt so much and really appreciate your channel. With regards to this video, I managed to recreate every filter and got them to work, apart from one. The date Filter. For some reason, the filter (when applied) removed all my dates in the column and left no lines. I checked everything close to a thousand times and after trying a few different options to filter between, I realised something... I have my PC set to UK Regional Settings so my dates are dd/mm/yyyy. For some reason, when filtering dates in Excel, it wanted me to use the US settings within my code... so got it to work with mm/dd/yyyy. How can this be fixed on any other regional settings?
HI and thanks for your comment Jack. Dates are no doubt very tricky. The key is to make sure ALL of your date formats are the same. That means your regional computer settings, your excel data settings, any excel default date formats, etc. No doubt the issue is incorrect formats, so once all the date formats are exactly the same it should work well. When filtering by dates its always best to use the date code #, for example I would use ">45325" which would mean greater than today (February 3rd) when using these numbers excel will be sure to get the correct dates. You can see these numbers when you enter a date then change the format to General
I hope this helps and thanks so much.
hi Randy again thanks for the great content. I had same problem with Aust Dates, still can't get any of the date sample to work, sorry just one 15/01/2023 - None of the others worked. tried manual entry checked system settings no date formats in format cells. Odd to say the least. I might try a fresh workbook tomorrow and redo see if that helps
Hi and thanks, you will want to make sure to set the date formats in your computer, excel and in the code to all the same format such as Format(YourDate,"mm/dd/yyyy")
I hope this helps and thanks so much.
Saludos desde México! Happy New Year!
Hi randy I follow you more than 2 years you are the best in Excel. So I want to know if there is a possibility to get annual subscription
Thank you so very much, I really appreciate that @allexgil. Happy New Year to you too :) :)
HI @messaoudpolitique9208 thanks so much for your comment. You can sign up for an annual subscription as a member here: www.patreon.com/ExcelForFreelancers
I hope this helps and thanks so much.
Quick question on using "OR" criteria. You had both criteria in the same column becuase the tested values where of the same variable. Suppose you had the Fruits and Price as the column headers and wanted to use an OR such as containing apple or anything ess than 5 on the price. Can this be done since the layout would have to include a blank criteria but then it is some what of a hodge-podge on syntax since it has both columns implying "and." So maybe this cannot be done?
HI Malcom, thanks so much. You would have to expand both the the columns and the rows of your criteria to include both OR and AND options. Yes it can get a bit tricky and for this basic lesson, I did not want to confusing people.
I hope this helps and thanks so much.
Hello Randy, can you explain what field is and criteria 1. How about field= 2 , what will happen
Hi and thanks so much. When using AutoFilter, and want to filter based on more than one condition you can have multiple fields such as FIeld=1 and Field=2, This allows you to filter based on more than 1 criteria. I hope this helps and thanks so much.
@@ExcelForFreelancers yeah, thanks
When I filter the Data, on the sheet1 there is 1 transaction was filtered, but when copy to sheet 2, there are 4 or more transactions. And even there are rows os "#N/A"' after the filtered rows. Could you help me with this problem? Thank you so much
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
how to extract data when there is a blank row between the table using (currentreligion) return from partial data to entire range for worksheet
Hi and and thanks you can use the last row of data using .Range("A99999").end (xlup).row
then run the advanced filter based on the full set of data. The blank row will be excluded
I hope this helps and thanks so much.
Until now you did not take about string concatenation that always appears in Sheet1.Range("A1:A" & LastRow).Value , I mean "A1:A" & LastRow
Hi and thanks very much. I do combine strings often in Excel. Please check any of my email templates as i combine variables with the subject or email body. I hope this helps and thanks so much.
Sir who is your mentor ??
Hmm good question. I have many mentors. From a marketing standpoint, Leila Gharani is pretty amazing, from a personal development, Jim Rohn was a pioneer and someone who inspired me to work online and create courses was Brendon Burchard. Thanks for the great question.
Seriously?? Leila....
She has been very helpful to me with Excel. But??? You're doing amazing things with Excel that I haven't seen Leila do yet.. @@ExcelForFreelancers
Yes, sure her and I, are very different in her approach to Excel, but she has 2.5 MILLION subscribers. That is insane! Of course her material is much more for a broader audience than mine but still, truly amazing, and her marketing and video editing are fantastic.
Thanks again for your continued support.
@ExcelForFreelancers You'll get to a million subscribers soon go beyond.. Keep your consistency up..
Leila has worked for over 7 years on TH-cam and sure has good marketing. Learn from her and thrive Sir. All the best Randy
Thank you so very much, I really appreciate that Nathaniel
رجاء حول اي شی یعنی من هاذا الشرح
مرحبًا وشكرًا، يتم استخدام هذا لإنشاء عوامل تصفية لبياناتك. سأعرض لك طرقًا متعددة لاستخدام هذه المرشحات. آمل أن يساعد هذا وشكرا جزيلا.
Randy I've written this code given below, is there anything wrong in this code? Because, when I run this code, nothing happens. No error is shown also. I can't understand where my error lies?
Option Explicit
Sub AdvancedFilter()
If Sheet1.FilterMode = True Then Sheet1.ShowAllData
Sheet1.Range("L1").CurrentRegion.Offset(1, 0).ClearContents
Sheet1.Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheet1.Range("F1:J10"), _
CopyToRange:=Sheet1.Range("L1:O1"), _
Unique:=False
End Sub
===============
I have a demo data ([Date], [Fruit], [Quantity], [Region]) starting from cell "A1" to cell "D51". My Criteria Range ([Date] , [Date] , [Fruit], [Quantity], [Region]) is from cell "F1" to cell "J10". And my Output Range ([Date], [Fruit], [Quantity], [Region]) is from cell "L1" to cell "O1".
Please Randy tell me, where is my flaw here?
Sorry Randy, It was my mistake. Finally I'm able to find out my mistake. Actually what happened, I wrote the date in "DD/MM/YYY" format in the Criteria Range. In our country, we write Date in "DD/MM/YYY" format. The moment I change the Date Format from "DD/MM/YYY" to "MM/DD/YYY", it gives me correct output.
Hi and thanks for your comment
Upon reviewing the code, here are some potential areas where the problem might lie:
1. Criteria Range Issues
The criteria range (Sheet1.Range("F1:J10")) must follow very specific rules for advanced filters.
The criteria headers (F1:J1) must match exactly the headers in the source data range (A1:D1), without any spelling differences or trailing spaces.
It's also important that the criteria provided within the range (F2:J10) are valid and aligned with the data.
2. Headers in CopyTo Range
The CopyToRange (Sheet1.Range("L1:O1")) should have headers that match exactly with those in the source data (A1:D1).
If the headers in L1:O1 don't match, the filter will not copy anything, and no error will occur either.
3. AdvancedFilter Requirements
Ensure that the output (CopyToRange) is not overlapping with the source range (A1:D51). Since the ranges do not overlap here, it should be fine.
Make sure there are no blank headers in the CopyToRange (L1:O1). Blank headers can also cause the filter to silently fail.
Possible Solution
Verify that your criteria range (F1:J10) has valid criteria that match the headers in A1:D1 and that they are formatted properly.
Ensure that L1:O1 matches the headers exactly from A1:D1.
I suggest adding some debug messages to check the state of the filter and to confirm that the regions are selected as expected. Here's an updated version of the code with debug messages:
Sub AdvancedFilter()
' Check if Sheet1 is available
If Sheet1 Is Nothing Then
MsgBox "Sheet1 is not found. Please check the sheet name.", vbCritical
Exit Sub
End If
' Clear any existing filter
If Sheet1.FilterMode = True Then
Sheet1.ShowAllData
End If
' Clear contents of output range
Sheet1.Range("L1").CurrentRegion.Offset(1, 0).ClearContents
' Debug message to check ranges
MsgBox "Copying from A1:D51 to L1:O1 with criteria in F1:J10."
' Apply the advanced filter
On Error Resume Next
Sheet1.Range("A1:D51").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheet1.Range("F1:J10"), _
CopyToRange:=Sheet1.Range("L1:O1"), _
Unique:=False
' Error handler in case the filter fails
If Err.Number 0 Then
MsgBox "Error in applying filter: " & Err.Description, vbCritical
Err.Clear
End If
On Error GoTo 0
End Sub
I hope this helps and thanks so much.
I am glad you were able to fix si thanks so much for sharing.
Date formats can be very tricky, especially with different regional settings.
@@ExcelForFreelancers Thank you Randy for guiding me. You are truly a gem. Once I complete your basic VBA training, I will start following your real life Projects.
Randy if you don't mind, would you kindly give me your WhatsApp number? From now onwards, I want to stay connected with you.
Hi and thanks very much. For questions, please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 60,000 Members who would love to help you with this. Thanks so much.