“Dear sir, Can you have a vb code in the condition that i want to hide (not to delete) the entire row if a particular cell has “katol”………. Please do it for me sir.“
I created this Excel-VB coding tutorial in response to the above request from Pravin which he sent via my Facebook page on Sunday 18th Sept 2016 as a case study for members of my Excel Heaven Visual Basic Automation Club
[NB: By way of interest, I’ve had to respond to Pravin’s request once before in this same manner – click here. At the bottom of this post, I reproduce a detailed message I initially sent to him, explaining why I may find it difficult to respond to future requests of this nature from him – pointing out a useful forum he can visit to get reliable answers to his queries.]
Below: A screenshot of the MS Excel Worksheet I formatted the code driven interface to fit into [Click here to download the workbook with the code]
Steps to follow
In order to to have a cell’s row hide and unhide based on the occurrence of a specific value, it’s best to write code for it in a Worksheet_Change event:
- Open a blank MS Excel workbook and click on the Worksheet Tab labeled Sheet1 to select it
- Next Press Alt+F11 to access the Visual Basic Editor
- In the Project Explorer Window, double click Sheet1 to open that Worksheet’s code window
TIP: You can compress steps 2 and 3 into one by simply right clicking on the Sheet1 Worksheet tab after step 1 and clicking VIEW CODE on the shortcut menu that appears, to open the Worksheet’s code window
- Copy and paste the code below into the Worksheet’s code window
====Starts====
Private Sub Worksheet_Change(ByVal Target As Range)
If Range(“CheckValue”).Value = “Katol” Then
Range(“CheckValue”).EntireRow.Hidden = True
ElseIf Range(“CheckValue”).Value <> “Katol” Then
Range(“CheckValue”).EntireRow.Hidden = False
End If
End Sub
====Ends====
- Recall from previous tutorials that I use range names to identify Excel objects I want to use code to refer to.
So, in the case of Pravin’s example, I’ve named the cell in which “Katol” appears, as “CheckValue” (the yellow coloured cell).
-
Note the buttons to the top left of this frame – for posting the test string into the yellow cell and for erasing the cell’s contents. The code behind them is reproduced in the explanation boxes below them.
[Click here to download the workbook with the code]
Below: Detailed message I initially sent to Pravin this evening, explaining why I may find it difficult to respond to future requests of this nature from him.
I pointed him to a useful forum he can visit to get reliable answers to his queries. I reproduce it here in order to give the reader greater insight into how I do what I do, as it relates to coaching members of my Excel-VB club while working as a multi-discplinary expert.
Hi Pravin.
It would be my pleasure to send you the code to hide a cell or range. But like I told you before, I do a lot of things that require my serious attention. For instance right now, I’m modifying my Excel-VB Poultry Farm Manager for a new buyer who requested changes, and after that I need to prepare 2 newsletters and also write 4 articles for a UK client. I simply do not have the kind of time others do, to respond to requests of this nature.
In other words, I’m NOT your usual Excel expert who does only Excel based work. I am a multipreneur, engaged in various vocations, that take my time.
But I have a vision to build Excel-VB developers in my primary target market of Nigeria/Africa. I happen to think YOU have lots of experts in your country or region you can reach out to.
To do what I do out here, I feel if I am to add value to someone, it is best that I empower him/her to be able, even if over time, to find out how to do such stuff and others s/he may desire to do, by him or herself
It is for this reason that I prefer to challenge any persons who reach out to me to commit to EXPERIENTIAL LEARNING. I do NOT believe in TELLING people how to do things. Instead i believe in guiding them to discover how to write their own code.
So, responding to requests like yours tend to be difficult for me, especially since you are NOT in my coaching club. Unlike what obtains in YOUR country and other non-African nations, access to the Internet here is such that I simply cannot make myself available to respond to all requests. It not only costs a lot, but it also tends to be erratic in nature – plus I have a lot I’m doing.
Most importantly, and I have told you this before, there are people who run websites who invite visitors to send them such requests. A good example is http://www.mrexcel.com/forum/index.php – the forum at Mr Excel. There you can register and post ANY questions you have for experts to answer.
In my case, I am primarily focused on building Excel-VB skills in niche markets in MY country, and my strategy does NOT require that I make myself available to respond to all comers, much as I would like to.
Instead, I have to focus on serving people within my network, based on their specific needs as business users. That is why my Excel-VB club is my priority.
I will therefore end by telling you what I have told you before:
I will make out time to create a tutorial based on what you specified above for you, which I will use to teach my club members, featuring YOU in my broadcast to them.
However, AFTER I do this, let me make it clear that I will NOT be able to take on ANY requests you make in future – except you choose to join my Excel-VB Club.
I hope my offer is acceptable to you. If not, I advise that you visit http://www.mrexcel.com/forum/index.php and post your question there.
Maybe in future I’ll be able to re-invent the way I work, to take on all comers. For now, this is the best I can do.
With kindest regards,
Tayo