Get macro to detect and hide blank rows
Solved/Closed
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
Val - Apr 5, 2010 at 02:07 PM
Related:
- Get macro to detect and hide blank rows
- How to hide app store on ipad - Guide
- How to know if someone hide their story from you on instagram - Guide
- Nvidia drivers auto detect - Guide
- Platinum hide ip - Download - VPN
- Spell number in excel without macro - Guide
36 responses
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?
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.
Thanks!
http://www.4shared.com/file/93090240/15a5f0d8/Rev11calorie_counter.html
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.
Thanks!
http://www.4shared.com/file/93090240/15a5f0d8/Rev11calorie_counter.html
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.
Rev.12
http://www.4shared.com/file/93113957/77168c51/Rev12calorie_counter.html
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.
Rev.12
http://www.4shared.com/file/93113957/77168c51/Rev12calorie_counter.html
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.
http://www.4shared.com/file/93126341/fa871711/Rev12calorie_counter.html
http://www.4shared.com/file/93126341/fa871711/Rev12calorie_counter.html
I've added summary 2 and put everything down in there.
Thanks!
Rev 13.
http://www.4shared.com/file/93294639/e69b403b/Rev13calorie_counter.html
Thanks!
Rev 13.
http://www.4shared.com/file/93294639/e69b403b/Rev13calorie_counter.html
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
http://www.4shared.com/file/93491050/2c173084/Rev16calorie_counter.html
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
http://www.4shared.com/file/93491050/2c173084/Rev16calorie_counter.html
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.
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.
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.
Rev.17
http://www.4shared.com/file/93873535/6e1717f7/Rev17calorie_counter.html
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.
Rev.17
http://www.4shared.com/file/93873535/6e1717f7/Rev17calorie_counter.html
The link didn't work, i did a search but couldn't find it?
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
http://www.4shared.com/file/94785091/694bdd9/Rev23calorie_counter.html
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
http://www.4shared.com/file/94785091/694bdd9/Rev23calorie_counter.html
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
http://www.4shared.com/file/94964551/916c884a/Rev26calorie_counter.html
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
http://www.4shared.com/file/94964551/916c884a/Rev26calorie_counter.html
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?
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.
http://www.4shared.com/file/95186138/e6fec087/Rev27calorie_counter.html
Tab key...Sorry, don't know about that.
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.
http://www.4shared.com/file/95186138/e6fec087/Rev27calorie_counter.html
Tab key...Sorry, don't know about that.
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.
Rev.28
http://www.4shared.com/file/95203074/bdceac2/Rev28calorie_counter.html
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.
Rev.28
http://www.4shared.com/file/95203074/bdceac2/Rev28calorie_counter.html
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?
One last thing, is it possible to have the arrow keys to work up and down on meal planner but not left or right?
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?
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
http://www.4shared.com/file/95621565/2853f003/Rev_31_calorie_counter.html
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
http://www.4shared.com/file/95621565/2853f003/Rev_31_calorie_counter.html
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.
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
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?
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?
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?
http://www.4shared.com/file/96867681/68f2253c/Diet_Plannerformatted.html
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?
http://www.4shared.com/file/96867681/68f2253c/Diet_Plannerformatted.html
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.
http://www.4shared.com/file/96896153/cf1dc6c/Diet_Plannerformatted.html
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.
http://www.4shared.com/file/96896153/cf1dc6c/Diet_Plannerformatted.html
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.
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.
Mar 15, 2009 at 03:05 PM
I see all number values in R:V and D:E. I'm not sure what you mean by comments.