Get macro to detect and hide blank rows

craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 - Mar 1, 2009 at 03:01 PM
 Val - Apr 5, 2010 at 02:07 PM
I want a macro to unhide about 20 blank rows copy values into the top row then hide the remaining rows (some cells have fill though) then the next time it will unhide the rows and copy values into the next available blank row and hide the remaining blank one. I know it sounds complicated, any ideas?


36 replies

Hi I forgot t say that when deleting foods from the add foods mode it only has to delete the values and comments from columns R:V the other columns will be formulas but it also needs to delete any comments only from columns D:E?
Sorry, but I am a little confused. When you click the add foods button, is that when you want it do delete also.
I see all number values in R:V and D:E. I'm not sure what you mean by comments.
Here you go, Rev11.
I've added my comments in your summary tab, hopefully that should answer your last question. Yes I am requiring the delete after you've clicked the add foods button.

Just realised what you meant by comments. I mean comments that you add with a right click add comments. This will be used for add additional info.
I added the Application.ScreenUpdating=False to the SaveMeal macro. This is so you won't see the screen
flicker. I copied the Meal Planner sheet, so it is called Meal Planner(2). In that sheet, I have a delete button
on the right side. this will give you a value of 0 for any cells that had a value for the columns you gave me. If the
cell was blank, then it will still be blank. Also, for column D, a value of 1 will appear if it had a comment.
Column E will have a value of 100 if it had a comment, otherwise the cell values for D and E will be 0.
I also added additional comments in the summary tab.

WutUp WutUp > WutUp WutUp
Mar 15, 2009 at 08:13 PM
I don't think my interpretation was correct on the delete. I just went ahead and created a Sub instead of a button. It is called DeleteMealPlanner. I think this is closer to what you want. What I don't know is if you want the 0's, 1's, and 100's whether the cells are already blank or not.
I've added summary 2 and put everything down in there.


Rev 13.
OK. I think I have it the way you explained. I have comments in the Summary2 sheet.
WutUp WutUp > WutUp WutUp
Mar 16, 2009 at 10:22 PM
I forgot to delete the code for the error you mentioned when I was troubleshooting. Use this one or you will
get an error if you try to use the save meal button.


Didn't find the answer you are looking for?

Ask a question
Thats absolutely fantastic, I can't tell you how grateful I am!
It's almost complete now, I've nearly finished all the formulas etc... and everything appears to work fine.
I misled you a bit on the debug error as I put the appostrophies in to let the debugger run past the faulty lines (A little trick I picked up after looking at the comments you put in the coding). I'll try it tomorrow at work and let you know.

There's only really one thing left (Besides the formatting in meal planner on A-Z sort but I don't think you know if it can be done or not)? And that's to do a macro to copy the day planner to "completed days" using the save plan button on the "day planner" tab. I've done a mock up example on the "completed days" tab so you can see how I'd like it to look if possible and the delete button should just get rid of the whole plan just like the day planner does for the saved meals. Obviously though because of the hidden cells due to meals per day there may be different amounts of rows!

Thank you so much!
Rev 16
Give me a day or two to look at the completed days. Also, don't worry about changing the code.
I realized that I will have to change the coding for several macros so it will be compatible with earlier
versions of excel. When I give you the revised copy, it will be saved as 97-2003 format so you can run it
on other computers that use earlier versions of excel.
WutUp WutUp > WutUp WutUp
Mar 17, 2009 at 07:22 PM
Did you create passwords for the sheets? I can't work on it without unprotecting the sheets.
Ah sorry yes the password is "1234"
The macro for the completed days sheet is Sub SavePlan, or if you are in the VB window, it is Module2.
I left the mock sheet that you used for the example in the workbook. You can delete it. If you look at the code, I put comments throughout. The only difference in the copied table I created vs. your mock sheet should be the delete button. I put it at the top of the table since each table copied could be varying sizes...(Easier to code that way). Also, I used the Protect method in the code to bypass the box to prompt for a password while I was testing. Let me know if you need it removed.

I'm not really sure about the formatting for the A-Z sort, but you can tell me your idea and I will see what
we can come up with.

WutUp WutUp > WutUp WutUp
Mar 19, 2009 at 09:54 PM
A couple of corrections.

Use this one.
craigo > WutUp WutUp
Mar 22, 2009 at 06:56 AM
Hi again

Looks like we're finally on the fine tuning, it's looking great.

I've puy all my notes in a summary tab and I've finally found a format that works for both 2003 & 2007.

Rev 20
WutUp WutUp > craigo
Mar 22, 2009 at 04:41 PM
I added comments to your summary sheet. The red X by any of the comments are issues that I will need
a couple of days to work on. In the meantime, you can see if all other issues are resolved.

WutUp WutUp > WutUp WutUp
Mar 22, 2009 at 05:25 PM
I just saw what you meant by the list creating duplicates. That should be corrected now. Comment in your summary sheet.
The link didn't work, i did a search but couldn't find it?
Sorry, I was in the process of replacing the file. The entire spreadsheet is complete, and I have comments by
all of the issues in the summary sheet. Check everything out to see if it is running how you want.

It's amazing, thank you!

When I adjust the meals per day it hides the rows even if there's data in them which makes the totals wrong. Is it possible to do something similar to the warning box on save meals?

The delete button on completed days can it be standard font i.e. not bold please?

Rev 23
OK, see if this is what you had in mind.

That's perfect, thanks.

One thing on the same feature, when you go from 6 to 4 days it still leaves the bottom row of 5 in?

One more problem that I've caused (sorry). I altered the code so that on the add foods / refresh it now hides column Y because I needed to add a bit of info in. Now when you add foods to the meal list (it seems 6 and above items) it causes an error when you save the meal?

When the list reaches its maximum (row 22), if you carry on trying to add does it affect anything? I haven't noticed anything but just want to check?

I don't know if you're interested in using the sheet but if you are all the formulas are now correct!

Rev 26
Just one thought. At the minute the add & delete cells work if you use the arrow keys to move the active into those cells. Is it possible to only make them work if they are clicked on with the mouse or highlighted and then enter key?
Doesn't matter if this can't be done, it was just a thought?
I had to adjust the coding for pasting the table because of the movement of the refresh button.
When the table was being copied, it was trying to copy the refresh button because it was in the range.
If the list is full, you will get a message box. If the save plan list is empty, you will get a message box.
Check it out. Let me know.

Tab key...Sorry, don't know about that.
WutUp WutUp > WutUp WutUp
Mar 26, 2009 at 08:33 PM
You can look at this also.
In (just) the Meal Planner tab, I have disabled the tab key, right, left, up, and down arrow keys.
If you were just wanting no access to the column where the delete buttons are, but allow to tab anywhere
else in the sheet, I don't know how to do that.

That's perfect.
One last thing, is it possible to have the arrow keys to work up and down on meal planner but not left or right?
OK, here you go.
Hmm just found another problem. you can't add comments unless the edit objects box is checked when you protect the sheet. I've done this but it keeps changing back when i run the macro?
In the macro code where you wanted to protect the sheets, DrawingObjects is set to true for the sheet protection.
I changed it to false for ALL the macros in modules and sheets.

I've used it properly in anger for the first time now and its great.
If it's possible though can you do a couple of tweaks for me.

1. Can the arrow keys work in every direction except left?

2. Day planner tab, can the cells with the saved meal names in remain unlocked (Editable)

3. Is it possible at all that when a meal is saved to the day planner tab the list of meals is organised alphbetically by meal name?

Rev 31
1. Arrow key just locked going left.

2. Cells with the saved meal names can be edited. You will notice that I had to unmerge the cells
to make that happen.

3. Sorry, don't know how to accomplish that.

craig0 > WutUp WutUp
Apr 2, 2009 at 11:37 AM
Ok I'll add a fresh post to try and find out about the sorting.

I want to thank you for everything that you've done for me, your knowledge has impressed me so much and I'm extremely grateful that you've taken the time to help me out. Thank you!
WutUp WutUp > craig0
Apr 2, 2009 at 06:08 PM
Glad I could help out. It was amazing how one post turned into this project. I work in manufacturing and I
work with over 1,200 SKU's daily, and have created many macros to simplify and expedite my workload.
It is always interesting to me to see what chalenges other people have with the data they work with.
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Apr 4, 2009 at 08:07 AM
Hi again, What are SKU's?

Just thinking, is it possible to copy data into a comment box? What I was considering is when a meal is saved copy the meal name and values over as normal but the list of ingredients gets put into a comment box enabling a-z sorting?
SKU stands for Stock Keeping Unit. A SKU is a individual product (or brand) that is produced.

I'm not understanding your question. Can you show an example of the result you are looking for?
Comments would just be additional info. for a cell. I don't see how to enable sorting.
craigo > WutUp WutUp
Apr 5, 2009 at 06:53 AM
What I was thinking was put the ingredients into an insert comment box but now I've done a mock up of it I've decided that it isn't the answer as it just looks messy and confusing.
I think I may just have to admit defeat on this one.
I have conditionally formatted the total cells now but when the day plan is saved to completed days it doesn't stay formatted properly, do you know why this is?
WutUp WutUp > craigo
Apr 5, 2009 at 10:22 AM
I see the columns for your conditional format reference are hidden in columns AG:AM.
You need to copy that reference to the completed days sheet also so the conditional format will work.

I added this code to the save plan (Module 2).

Dim CpyRng As Range

Set CpyRng = d.Range("AG12:AM14")

CpyRng.Copy Destination:=c.Range("AG12:AM14")

c.Columns("AG:AM").Hidden = True

See if it is working as you are expecting.
That's perfect, thank you yet again :-)
I need a macro that will delete blank rows without disrupting the template.
For example, rows A1-A5 all have complete data. Row A6 has no amount associated with its data and therefore should be deleted. Row 7-10 all have complete data. Each row has data linked from another worksheet. I would like to remove Row A6 without disrupting the data in Row 7-10. Any ideas.