HELP! Hiding empty Rows via VBA Code.

**wedzmer** · 07-10-2014, 09:32 AM

Hello Everyone!

Once again, I need help on my worksheet...

I need help on automatically hiding empty rows on my table... though the table has been referenced via string of formulas through the help of our members here which I added up on my worksheet.. Some of the rows on my table are empty and I tried a VBA code on automatically deleting the rows but then I realized that if that certain 'referenced' string of that cell is not empty anymore, that would cause me much trouble.. so basically I need a code that would automatically hide the rows that displays a result of "" and unhides when it is not empty.

Thanks!

**nathansav** · 07-10-2014, 11:20 AM

Please Login or Register  to view this content.

sorry not in excel at the minute

**wedzmer** · 07-10-2014, 12:40 PM

thank you for your response, but it doesn't work..

Maybe because my table is from Cell A10:AA66, and what I need for the VBA code to check is if there are any empty cells on Column A, then all the rows on that cell would automatically hide... It's because there are certain cells where in it is not empty on the column A but is empty on column D.. what I'm looking for is for the VBA to check if all the cells from column A to column AA are all empty then it will hide that row.. but if it has at least 1 cell that is not empty, then it'll leave it as it is...

**Winon** · 07-10-2014, 01:57 PM

Hello wedzmer,

Would any of those empty cells contain formulae which may return nothing, and do you want to retain numbers or text in the rows which are not to be hidden or both?

Regards.

**wedzmer** · 07-10-2014, 03:18 PM

Hi Winon!

Yes, those empty cells contain formulas that returns "" values and yes, I do want those rows which contain numbers and/or text to be unhidden if the reference of the worksheet are filled with text or numbers in any of the columns between A to AA.

Thank you!

**Winon** · 07-10-2014, 04:49 PM

Hello wedzmer,

Please try the attached sample Workbook, to see how it can be done.

Play around with it, take out what you don't like, or whatever.

Regards.

**wedzmer** · 07-10-2014, 08:28 PM

Hello Winon!

Again, you do make it look like so easy..

But I'm not really sure I understand how to use the codes here and apply it on my worksheet..

**Winon** · 07-11-2014, 05:01 AM

Hello wedzmer,

You really seem to be on a learning curve here.

Upload the Workbook with the sheet you want the code applied to, and I shall show you, how it is done.

Regards.

**wedzmer** · 07-11-2014, 07:44 AM

Hello winon!

I was working with 2 files here. The first file is the original file that was sent to me by our School Head which I renamed to MY SF1 in order for the values on my other file named SCHOOL FORMS to referenced with. Everything was doing okay until I was sent a new file by our head which had 46 Boys and 11 Girls... This caused me to panic because the design I encoded and that which you engineered with for my School Forms were only fitted for 30 Boys and 30 Girls maximum plus having the inconsistent number of learners would also mean I will have a bunch of empty rows that again prompted me with this question on this thread. Here are the two files I worked on, MY SF1 (which was my first test file) and the School Forms 2014 file that I encoded and placed the formulas I learned from here in the site.

School Forms 2014.xlsx & MY SF1.xls

Again.. Thanks to everyone who help, especially to you sir!

Much gratitude,

Wedzmer

**Winon** · 07-11-2014, 11:19 AM

Hello wedzmer,

Here are your Workbooks back again. See if it works for you, and if you are satisfied with the results, then please mark this Thread as Solved.

Thank you.

**wedzmer** · 07-11-2014, 11:40 AM

Hi Winon,

Thanks again, but I quickly noticed that the file size of MY SF1 changed from 54KB to 88KB... did you change anything about the file?

**Winon** · 07-11-2014, 12:05 PM

Hi wedzmer,

Yeah, sorry man, I forgot to mention that I have increased the Files to cater for 60 Male and Females respectively. Just to stay on the safe side.

**wedzmer** · 07-11-2014, 12:11 PM

Hi Winon!

Question: Does this mean that if there's a new file that will be used, I need to change it also in order for it to be used by the School Form 2014 you created?

**Winon** · 07-11-2014, 12:17 PM

Yes, Sir.

I should be able to amend the School Form 2014, for you over the weekend.

**wedzmer** · 07-11-2014, 12:24 PM

Would it be okay if I'd just wait for the end of the weekend? I don't really have much choice because I don't have full control of the files that would be sent to my fellow volunteers and all of us are noob in excel.

^_^

**Winon** · 07-11-2014, 12:32 PM

Yes, thats fine.

**wedzmer** · 07-11-2014, 01:13 PM

Thanks a lot winon!

God Bless!!!

**wedzmer** · 07-13-2014, 10:30 PM

Hello Winon!

We're you able to fix it?

**Winon** · 07-14-2014, 05:09 AM

Hello wedzmer,

I did reply to you, yesterday morning in the original thread, "Excel cell color background shading using if statements new-post.html"

Quote below:

With the Workbook (Attendance Test Form 2 -Update3), we are working on, to increase the total number of scholars to 60 for male and female respectively, in Sheet SF3, you are referencing to another Worksheet named, Student Information'!, in a Workbook called School Forms 2014.xlsx which is located on your system in a Folder called "School Forms".

I have not received any Workbook from you that contains a Sheet called Student Information.

On my side, after I have increased the totals to 60, it now returns a #Ref! Error, which makes it impossible for me, to complete all the upgrades to the Workbook.

Could you please increase the totals to 60 each for male and female of that Student Information Sheet, and send it to me?

Thank you.

Regards.

**wedzmer** · 07-14-2014, 08:39 AM

Hello Winon,

I'm really sorry.. I did not pay attention..

Since Saturday, I was only looking at the "Latest Started Threads" of my profile and every time I look at it, I see that the last post on the thread Excel-Cell background Shading" was my name so I thought you didn't reply.. Even up to now, I still see that it was my name who sent the last post... here's a screenshot of that which I just did right now...

Untitled.jpg

I'm Really sorry.. but I did check the thread right now and saw your reply there... I'm really really sorry...

And about that the "student information sheet", I actually discarded that sheet already because I thought I could just reference the SF3 to the SF1 which you were working on... I mean, if you finish the SF1, I was thinking that maybe I could change the reference and that the same "hiding codes" you used in SF1 will be used in SF3 in cases wherein there would be empty rows... the same principle for SF2 for hiding rows...

Could you please increase the totals to 60 each for male and female of that Student Information Sheet, and send it to me?

I have attached here the latest file sent to me by my co-volunteer who had 50 boys and 18 girls, and we were asked to complete the missing data, and correct if there are any errors in the LRN Number of each student, spelling of th..I also changed the name of the file to MY SF1 because I experimented with it later this morning... hehehe.. you know, trying to use the VBA codes you used.. but again, unfortunately, I can't even make it work.. lol
I'm such a noob...

MY SF1.xls

GOD BLESS ALWAYS!!!

/PS/

I promise next time I'll check the threads even if it says I was the last one who posted.. really sorry about me being neglectful...

**Winon** · 07-14-2014, 08:58 AM

Hello wedzmer,

Thank you for the clarification.

And about that the "student information sheet", I actually discarded that sheet already because I thought I could just reference the SF3 to the SF1 which you were working on...

I shall reference SF3 instead of the "student information sheet", and you can change it as you like, if needed.

I'll get back to you as soon as I can.

Regards.

**wedzmer** · 07-14-2014, 09:15 AM

Sir Winon,

Thanks a lot!

I hope you could teach me those codes and how to use it (the hiding thing)...
I wish to use it on SF5 too.. though the file is still yet to be sent to us, and its format...

God speed

**Winon** · 07-15-2014, 06:53 AM

Hello wedzmer,

Please find attached revised Workbook, as requested.

I trust that you will be able to see, and learn from all the other sample Workbooks, how your issues were dealt with, and that you could now do it for yourself.

Regards.

**wedzmer** · 07-15-2014, 11:26 AM

Hi Winon!!!

Thanks a lot for this... i thought you were going to edit the School Forms 2014 and not the attendance sheet...
I find SF2 very complicated... :'(

**Winon** · 07-15-2014, 03:44 PM

Hello wedzmer,

It seems that we may have gotten our lines crossed somewhere along the Posts.

From the Post: http://www.excelforum.com/excel-gene...-new-post.html

I have simply increased the SF2 (JUNE) file to cater for 60 Male, and 60 Female respectively, and added two buttons to Hide or Show Blank lines. Nothing complicated really. You have asked for Cells to be shaded in a certain way, and it is still doing so.

As for the School Forms and the My SF1 Files, there was not much to do except for also increasing the number to 60 each for Male and Female, and also to Hide or Show Blank Rows.

I have attached the latter two Workbooks for you again.

Regards.

**wedzmer** · 07-16-2014, 04:41 AM

Hello Winon,

Thanks a lot! I really learned a lot here especially from you.

I would like to ask why if I use the codes you used in MySF1 file, i mean the vba codes, it won't work.

I copied the codes in mysf1 on a notepad and then changed the reference and the source of the cells etc in my sf5 workbook but when i click 'run', nothing happens. I also tried yo save it as a macro-enabled workbook, but still with no avail. Did i miss something or anything wrong with what I did? And also, i don't see those 'hide' and 'unhide' buttons when i run it.

Much respect!

Wedzmer

**Winon** · 07-16-2014, 05:59 AM

Hello wedzmer,

Use the "Create New Sheet" Button in the attached sample Workbook, to create new Sheets.

Regards.

**wedzmer** · 07-16-2014, 12:22 PM

Hi Winon,

I was hoping to use the codes on a different workbook.

**Winon** · 07-16-2014, 12:38 PM

Hello wedzmer,

Copy the VBA code from the last My SF1 I had sent you, and paste it into the Code for the other Workbook, and then change ranges to suit. When you are done with that, you will have to insert your own Buttons, assign the appropriate Macros to it, and it should work.

Regards.

**wedzmer** · 07-16-2014, 01:43 PM

Hello Winon,

So the buttons are manually made, it doesn't just pop up after the codes are pasted?

Well, I thought it was some sort of magic that shows up just like that...

Does the buttons in MY SF1 file also have macros that can be copied? I mean, should there be any changes if I use it on other workbooks too?

HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Re: HELP! Hiding empty Rows via VBA Code.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Hiding Rows using code

[SOLVED] How to code Macro for hiding rows

Bookmarks

Bookmarks

Posting Permissions