+ Reply to Thread
Results 1 to 17 of 17

Hide - Un Hide rows with empty columns

  1. #1
    Registered User
    Join Date
    04-04-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    12

    Wink Hide - Un Hide rows with empty columns

    Hi all,
    I' newbie in VBA, so please apologise if the below question is stupid! . I searched in the forum for a solution to my problem but wasn't able to find nothing.
    I have a simple problem I think :P and I hope your help.
    I have a workbook with more then 1 sheet filled in the some way (in the file I attach only one sheet is filled).

    In each sheet I would like to hide and un hide all the rows that are empty using VBA by 1 or 2 buttons it is not the matter.

    The first two columns have a formula as you can see that give numbers when the cells c2:AJ15 are filled by a letter.

    I would like to hide the rows that shows empty cells in the range c2:xxXX (where xx is the max columns lenght in the file is AJ15). The MAX rows length is greater that I show in the file.

    I'll appreciate any help
    Thank you in advance
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Hide - Un Hide rows with empty columns

    To hide rows, try this code.......
    Please Login or Register  to view this content.

    And to Unhide Rows, try this code......
    Please Login or Register  to view this content.
    Hope that helps.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    04-04-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Hide - Un Hide rows with empty columns

    Thank you very much it works perfect.
    I add two buttons on the sheet and linked to each one to the corresponding macro

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,710

    Re: Hide - Un Hide rows with empty columns

    Another macro alternative to sktneers solution

    Please Login or Register  to view this content.
    and to unhide

    Please Login or Register  to view this content.
    Alf

  5. #5
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Hide - Un Hide rows with empty columns

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Appreciate the help? CLICK *

  6. #6
    Registered User
    Join Date
    04-04-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Hide - Un Hide rows with empty columns

    Hi Alf and Az,
    very appreciate your help.
    Can you please explain me the difference between your solution and the sktneet one?
    I'm learning
    BDF

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Hide - Un Hide rows with empty columns

    Glad to help you. Thanks for the feedback.

  8. #8
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,710

    Re: Hide - Un Hide rows with empty columns

    Can you please explain me the difference between your solution and the sktneet one?
    In respect to the final result no difference in both cases "hide" and "unhide".

    The only difference in the "hide" macro is that sktneer tests each row from the C column to last used column to see if row is empty if so hide row.

    I use the IsError function to check every cell in column A for "#N/A!" error and if so hide row. He checks from top to bottom I check from bottom to top.

    In the "unhide" macro sktneers macro selects the used range by finding the last row with a value selects the range C2 to last C row used and unhide this range.

    I use the VBA command "ActiveSheet.UsedRange" to select the range and then unhide rows. There is a part not needed in my macro it will work just as well without using the offset command i.e.

    Please Login or Register  to view this content.
    Alf

    Ps Thanks for rep!
    Last edited by Alf; 04-13-2014 at 07:31 AM.

  9. #9
    Registered User
    Join Date
    04-04-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Hide - Un Hide rows with empty columns

    Hi Alf,all
    I tried to extend your suggestion to a more complex worksheet and I can't get the some results.
    What I'm doing wrong?
    Thank you in advance
    Attached Files Attached Files

  10. #10
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Hide - Un Hide rows with empty columns

    If you are following Alf's code, you need to make some changes in the code as the layout of your sheet has been changed. Try this......
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-04-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Hide - Un Hide rows with empty columns

    Hi Sktneer,
    thank you I done the change you suggest on another pc changing A with D but it didn't work all day.
    Now I tried on another pc and it works!!!! I'll go crazy!
    Thank you
    B

  12. #12
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Hide - Un Hide rows with empty columns

    The code is running well on the attachment you provided in the post#9, I checked it. Where is the problem then?
    Did you exactly copied the whole code given in post#10 ? Because there is one more line where code is changed and which is "If IsError(Cells(i, 4)) Then".
    But you told that your code is running on one pc and not on another. Strange...

  13. #13
    Registered User
    Join Date
    04-04-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Hide - Un Hide rows with empty columns

    Yes it is strange also to me. I checked on my pc and it works tomorrow I'll check on the other one!

  14. #14
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,710

    Re: Hide - Un Hide rows with empty columns

    Hi sktneer

    Tanks for help in sorting this problem out. As the layout is changed one can also change the "To 2" since data now starts at row 7.

    Please Login or Register  to view this content.

    Hi BDF as far as I can see sktneer have sorted this problem out also on my PC this file runs without any problem. A thing to consider is that macros are "build to order" so if the layout is changed most macros fell flat on their feet (as far as macros have feet).

    The "idea" behind this macro was to find cells with errors. I the previous file the "error" cells was found in column A and B and I used column A. In the new layout the "error" cells are found in column D so sktneer did an adjustment for that.

    The "Unhide" macro is more "robust" since it works on the "used range" so this macro will still unhide rows even if layout is changed.

    Alf
    Last edited by Alf; 04-14-2014 at 01:47 PM.

  15. #15
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Hide - Un Hide rows with empty columns

    @ Alf

    My pleasure.

  16. #16
    Registered User
    Join Date
    04-04-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Hide - Un Hide rows with empty columns

    Hi both,
    @Alf thank you I understood the idea also in the morning before to ask for your help again, but for some reason on the pc I have in the office it didnt work. So I asked you again. The new formula correct run on my home pc ....
    I corrected also for 7 instead 2, but it works in both cases
    Thank you very much guys!
    Boris

  17. #17
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,710

    Re: Hide - Un Hide rows with empty columns

    Glad this is sorted out!

    Alf

+ 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] Show/Hide Empty rows/columns
    By omfe in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-23-2012, 10:10 AM
  2. [SOLVED] Hide empty rows of shown columns
    By wilco in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-17-2012, 05:42 PM
  3. Hide columns if rows empty
    By melewis212 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-25-2012, 06:55 PM
  4. Un hide non empty columns
    By hamad.fatima@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2006, 03:28 AM
  5. [SOLVED] URGENT!! Use macro button to hide empty columns and rows...HELP!!!!
    By Hawk in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-08-2005, 06:05 PM

Tags for this Thread

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