{"id":10452,"date":"2016-09-18T18:52:36","date_gmt":"2016-09-18T18:52:36","guid":{"rendered":"http:\/\/tayosolagbade.com\/sdnuggets\/?p=10452"},"modified":"2016-09-18T18:52:36","modified_gmt":"2016-09-18T18:52:36","slug":"excelvb-case-study-code-to-hide-the-row-for-a-cell-that-contains-a-specific-text-string","status":"publish","type":"post","link":"http:\/\/tayosolagbade.com\/sdnuggets\/excelvb-case-study-code-to-hide-the-row-for-a-cell-that-contains-a-specific-text-string\/","title":{"rendered":"ExcelVB Case Study: Code to hide the row for a cell that contains a specific text string"},"content":{"rendered":"

\"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.<\/span>\"<\/p><\/blockquote>\n

\"\"<\/a><\/p>\n

I created<\/i> 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<\/i><\/p>\n

[NB: By way of interest, I've had to respond to Pravin's request once before in this same manner - click here<\/a>. 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.]
\n<\/i><\/p>\n

Below: A screenshot of the MS Excel Worksheet I formatted the code driven interface to fit into [Click here<\/a> to download the workbook with the code]<\/h3>\n

\"pravin-case-study\"<\/a><\/p>\n

\n

Steps to follow<\/b><\/h3>\n

In order to to have a cell's row hide and unhide based \u00a0on the occurrence of a specific value,\u00a0 it's best to\u00a0 write code for it in a Worksheet_Change <\/b>event:<\/p>\n

    \n
  1. Open a blank MS Excel workbook and click on the Worksheet Tab labeled Sheet1<\/b> to select it<\/li>\n
  2. Next Press Alt+F11 to access the Visual Basic Editor<\/li>\n
  3. In the Project Explorer Window, double click Sheet1<\/b> to open that Worksheet's code window<\/li>\n<\/ol>\n

    TIP:\u00a0 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 <\/b>Worksheet's code window<\/p>\n

      \n
    1. Copy and paste the code below into the Worksheet's code window<\/li>\n<\/ol>\n

      ====Starts====<\/b><\/p>\n

      Private Sub Worksheet_Change(ByVal Target As Range)<\/b><\/p>\n

      \u00a0\u00a0\u00a0 If Range(\"CheckValue\").Value = \"Katol\" Then<\/b><\/p>\n

      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Range(\"CheckValue\").EntireRow.Hidden = True<\/b><\/p>\n

      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/b><\/p>\n

      \u00a0\u00a0\u00a0 ElseIf Range(\"CheckValue\").Value <> \"Katol\" Then<\/b><\/p>\n

      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Range(\"CheckValue\").EntireRow.Hidden = False<\/b><\/p>\n

      \u00a0\u00a0\u00a0 End If<\/b><\/p>\n

      End Sub<\/b><\/p>\n

      ====Ends====<\/b><\/p>\n

        \n
      1. Recall from previous tutorials that I use range names to identify Excel objects I want to use code to refer to.<\/li>\n<\/ol>\n

        So, in the case of Pravin's example, I've named the cell in which \"Katol\" appears, as \"CheckValue\" (the yellow coloured cell).<\/p><\/blockquote>\n

          \n
        1. \n

          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.<\/p><\/blockquote>\n<\/li>\n<\/ol>\n

          [Click here<\/a> to download the workbook with the code]<\/p>\n

          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><\/h3>\n

          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.<\/i><\/p>\n

          Hi Pravin.<\/p>\n

          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.<\/p>\n

          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.<\/p>\n

          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.<\/p>\n

          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<\/p>\n

          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.<\/p>\n

          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.<\/p>\n

          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<\/a> - the forum at Mr Excel. There you can register and post ANY questions you have for experts to answer.<\/p>\n

          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.<\/p>\n

          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.<\/p>\n

          I will therefore end by telling you what I have told you before:<\/p>\n

          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.<\/p>\n

          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.<\/p>\n

          I hope my offer is acceptable to you. If not, I advise that you visit http:\/\/www.mrexcel.com\/forum\/index.php<\/a> and post your question there.<\/p>\n

          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.<\/p>\n

          With kindest regards,<\/p>\n

          Tayo<\/p><\/blockquote>\n

          Related Article<\/h3>\n

          Download MS Excel-VB Screen shot Demonstration Video Tutorials for Tayo Solagbade\u2019s Introduction to Excel-VB Automation Workshop Held on Sat 19th March 2016 in Lagos-Nigeria [Includes video recorded in response to last week\u2019s request for Excel-VB coding help from a business user in India]<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

          \"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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,1375,266,1440,10],"tags":[2502,2501],"class_list":["post-10452","post","type-post","status-publish","format-standard","hentry","category-employeescareer-persons","category-excel-vb-solutions","category-my-dn-travel-news","category-recommended-websites-articles","category-self-development","tag-code-to-hide-the-row-for-a-cell-that-contains-a-specific-text-string","tag-excelvb-case-study"],"aioseo_notices":[],"views":1143,"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/tayosolagbade.com\/sdnuggets\/wp-json\/wp\/v2\/posts\/10452","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/tayosolagbade.com\/sdnuggets\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/tayosolagbade.com\/sdnuggets\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/tayosolagbade.com\/sdnuggets\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/tayosolagbade.com\/sdnuggets\/wp-json\/wp\/v2\/comments?post=10452"}],"version-history":[{"count":3,"href":"http:\/\/tayosolagbade.com\/sdnuggets\/wp-json\/wp\/v2\/posts\/10452\/revisions"}],"predecessor-version":[{"id":10463,"href":"http:\/\/tayosolagbade.com\/sdnuggets\/wp-json\/wp\/v2\/posts\/10452\/revisions\/10463"}],"wp:attachment":[{"href":"http:\/\/tayosolagbade.com\/sdnuggets\/wp-json\/wp\/v2\/media?parent=10452"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/tayosolagbade.com\/sdnuggets\/wp-json\/wp\/v2\/categories?post=10452"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/tayosolagbade.com\/sdnuggets\/wp-json\/wp\/v2\/tags?post=10452"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}