+ Reply to Thread
Results 1 to 19 of 19

hide all rows with a cetain word

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    hide all rows with a cetain word

    I have a word "MISC" on column C, can someone write me a vba code to hide ALL rows that do not contain that word?

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: hide all rows with a cetain word

    Why not just use the built in Filter?

    Please Login or Register  to view this content.
    HTH
    Regards, Jeff

  3. #3
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: hide all rows with a cetain word

    I found this link https://www.ozgrid.com/forum/forum/h...pecified-value
    bu it hs a name range, and I have it from row 2 to row 652 (its dynamic rows)

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: hide all rows with a cetain word

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: hide all rows with a cetain word

    actually the word is "NC" on column c

    so I changed the code to
    Please Login or Register  to view this content.
    but it hides everything.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: hide all rows with a cetain word

    Please add a representative sample to build from with a clear before and after.

    Also, turn on the filters, then turn on the macro recorder and record what you filter. The code will be produced.

  7. #7
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: hide all rows with a cetain word

    this code works:
    Please Login or Register  to view this content.
    but how come it takes so long? its goin to every cell, is thre a quicker way please?

  8. #8
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: hide all rows with a cetain word

    how do I unhide it? and reverse everything?
    sorry never mind,i just grab a copy and put it back .
    Last edited by chubbychub; 11-22-2018 at 03:45 PM.

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: hide all rows with a cetain word

    Yes, use the built in filter feature or use the code to filter.

    If you use the above, you are looping thru all the cells, whereas, the filter code is a one shot filter.

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: hide all rows with a cetain word

    Quote Originally Posted by chubbychub View Post
    how do I unhide it? and reverse everything?
    That's up to you and how you want the user to interact with your spreadsheet. What do you want to happen? In other words, what action would you expect the user to take to initiate the hide/unhide?

    I don't know your spreadsheet.

    A sample spreadsheet can always help too...

  11. #11
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: hide all rows with a cetain word

    the below code works, but it loops thru each cell, however I think I can deal with the wait, how can I put everything back after its printed?
    Please Login or Register  to view this content.
    I have created a button to run that macro, but would like if possible, after that sub, it can unhide everything back.
    Last edited by chubbychub; 11-22-2018 at 05:06 PM.

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: hide all rows with a cetain word

    Try...

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: hide all rows with a cetain word

    Cells.EntireRow.Hidden = False , I tried adding the code at the bottom and it didn't worked.

  14. #14
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: hide all rows with a cetain word

    i have been working all nite on it, and this is how far i got, but i am getting and error:
    Please Login or Register  to view this content.
    plese help so i can go to sleep. thank you.

  15. #15
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: hide all rows with a cetain word

    When you hit debug, what line shows the error?'

    Can you post a sample test workbook?

  16. #16
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: hide all rows with a cetain word

    this code works, i was trying to make a separate line (i used macro recorder)
    Please Login or Register  to view this content.
    but i don't know what the difference is in between them both.

  17. #17
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: hide all rows with a cetain word

    Please Login or Register  to view this content.
    In this line of code above, you have broken the string. So, when you rewrite it too...

    Please Login or Register  to view this content.
    ...it is now a continuous thought in the VBA language.

    Also, I see as a difference...

    Please Login or Register  to view this content.
    This is good. You have to select the cells before you can act on them, however, you should be able to rewrite that as...

    Please Login or Register  to view this content.
    http://www.tushar-mehta.com/excel/vb...acro_recorder/

    Very rarely in VBA do you have to select something to do something to it.
    Last edited by jeffreybrown; 11-23-2018 at 07:03 PM.

  18. #18
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: hide all rows with a cetain word

    Thank you jeffreybrow!!! that works, and i understand now. thank you very much again, thread marked as solved and rep added! thanks.

  19. #19
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: hide all rows with a cetain word

    You're welcome and thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] converting only cetain sheets to pdf
    By chubbychub in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-06-2017, 09:31 PM
  2. [SOLVED] Macro to hide Sheets containing the word Jun but not the word JUNE
    By oneblondebrow in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-06-2017, 07:51 AM
  3. deleting rows with a cetain cell valu
    By toddp2 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-29-2017, 06:07 PM
  4. [SOLVED] Skip cetain predefined texts in the row
    By Sekars in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2016, 05:15 AM
  5. Hide Rows with a Value (2 values to hide) then delete visible rows
    By Slea in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-19-2013, 06:25 AM
  6. [SOLVED] autosave after cetain macro is run
    By miyachow in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-27-2013, 09:25 AM
  7. Using check boxes to hide and un-hide rows with drop down lists within rows
    By Sparky_Chris in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2012, 07:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1