Hide - Un Hide rows with empty columns

**BDF** · 04-13-2014, 02:54 AM

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

**sktneer** · 04-13-2014, 03:28 AM

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.

**BDF** · 04-13-2014, 03:50 AM

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

**Alf** · 04-13-2014, 04:14 AM

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

**AZ-XL** · 04-13-2014, 04:20 AM

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**BDF** · 04-13-2014, 05:03 AM

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

**sktneer** · 04-13-2014, 05:09 AM

Glad to help you. Thanks for the feedback.

**Alf** · 04-13-2014, 07:28 AM

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!

**BDF** · 04-14-2014, 11:17 AM

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

**sktneer** · 04-14-2014, 11:25 AM

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.

**BDF** · 04-14-2014, 11:34 AM

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

**sktneer** · 04-14-2014, 11:40 AM

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...

**BDF** · 04-14-2014, 11:45 AM

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

**Alf** · 04-14-2014, 01:44 PM

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

**sktneer** · 04-14-2014, 01:47 PM

@ Alf

My pleasure.

**BDF** · 04-14-2014, 01:59 PM

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

**Alf** · 04-14-2014, 02:09 PM

Glad this is sorted out!

Alf

Hide - Un Hide rows with empty columns

LinkBack

Thread Tools

Rate This Thread

Display

Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Re: Hide - Un Hide rows with empty columns

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Show/Hide Empty rows/columns

[SOLVED] Hide empty rows of shown columns

Hide columns if rows empty

Un hide non empty columns

[SOLVED] URGENT!! Use macro button to hide empty columns and rows...HELP!!!!

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions