How to remove blank cells that contain formula using VBA code
Hi guys,
I'm new to vba and would like to know a code where in autofilter ignores a blank cell with formula.
Leave Tracker-Forum.xlsm
as you can see on the attachment, my code does the autofilter but won't ignore the vlookup formula from row 6 til row 588.
is there a way for me to do the auto-filter and still start from row 6 onwards ?
Been searching for it a long time and cant seem to find a solution to it.
When I use your two search functions they are searching based on the VALUEs in columns B and C. The fact that there are formulas in C seem to be irrelevant.
What is the actual problem you are encountering? Answer with specific steps on what to type to cause the problem you're observing, describe the problem that results from what you typed. Currently things appear to work correctly.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi.
Thank you for the prompt response. I am attaching a screenshot on the search function:
Screenshot1.png
the issue is that the autofilter function WORKS, but whenever it filters the next blank row starts at 589 and not at row 6:
Screenshot2.png
the tracker im trying to make works by entering the Employee ID on column "B" and vlookup will give the result for columns C to E based on Vlookup Sheet. the issue im trying to fix is how can i make the autofilter function ignore the blank cells with conditional formula so that i can start entering the next leave data of the employee to row 6 onwards. is that even possible?
No, I don't think so. An Autofilter treats the entire "region" of a table as relevant. Since you have prefilled some of the columns all the way down 500 rows with a formula, then THAT is the current "region".
Hmm, having said that, the only reason it's doing that is because of the formulas. Since you're using VBA, there's no reason not to remove all those formulas and replace their functionality with a worksheet_change event that watches column B for changes. If you enter a new row value, the VBA would go and retrieve all the information you're currently using the formulas to accomplish in that row only, no further.
One way to accomplish that is to have the VBA enter the working formulas in those cells:
1) Remove ALL the formulas beyond row 4
2) Then put this code into the Admin Leave Tracker sheet module, replacing all the code that is in there currently:
Please Login or Register to view this content.
Works like charm! Thank you very much for the solution!
Now I have to analyze the data you have given to me . Again, Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks