get macro to detect and hide blank rowsSolved/Closed

Question

Hello,
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?

Craig

WutUp WutUp · Answer

You want to unhide 20 blank rows? Does that mean there are blank rows in between your data?

Maybe something like this.

Row 1  Data...........................
Row 2  Data...........................
Row 3 thru 23  Hidden...........
Row 24 More data..................
Etc.....

craigo · Answer

Hi and thanks for your reply. This is my macro but what I need it to do (and I know it sounds complicated) but it has to copy the data as said then insert a row above it, unhide the 20 rows below it, delete one of them and rehide them. Is this possible at all???


Sub Macro1()

Dim j
j = 2

ActiveSheet.Unprotect
Cells(j, 2) = ActiveCell.Offset(0, 1).Value
Cells(j, 3) = ActiveCell.Offset(0, 2).Value
Cells(j, 4) = ActiveCell.Offset(0, 3).Value
Cells(j, 5) = ActiveCell.Offset(0, 4).Value
Cells(j, 6) = ActiveCell.Offset(0, 5).Value
Cells(j, 7) = ActiveCell.Offset(0, 6).Value
Cells(j, 8) = ActiveCell.Offset(0, 7).Value
Cells(j, 9) = ActiveCell.Offset(0, 8).Value
Cells(j, 10) = ActiveCell.Offset(0, 9).Value
Cells(j, 11) = ActiveCell.Offset(0, 10).Value
Cells(j, 12) = ActiveCell.Offset(0, 11).Value
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("3:3").Select
Selection.Copy
Rows("2:2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

craigo · Answer

I think we're getting close and I'm trying to understand the coding but struggling. I had to take the word private out at the beginning to make it work and altered the row range to 22 and it works except what the macro is doing is putting data into a list but with the coding as it is at the minute its inserting one row of data then deleting it when it inserts the next, where as i need it to delete the next blank row (which is hidden) and not hide any of the inserted data. Any ideas? Also is it possible to return the cursor back to the cell where it copied the data from, obviously this isn't the same cell everytime?

Thanks for your help with this...

Craig

craigo · Answer

If you let me know how I can provide you with a sample sheet then i can do this. Meanwhile, basiically i have 20 rows hidden and one above it which is not, this is where the data is copied into then a row is inserted above it and i need only one of the blank rows below it deleted. The rows are basically hidden so that the formula below them that adds up the totals aren't affected and the data below that stays static too.

craigo · Answer

ok I've registered with the site and uploaded the file. The page you need is meal counter, hopefully you'll see from there what I'm trying to do. Thanks!

craigo · Answer

Sorry, this file sharing is all new to me. The file is called: calorie counter hidden row macroplanner2.xlsm. Let me know if you need any more info.

craigo · Answer

I'm not sure what's it's not doing now. the way the list is forming is perfect, exactly what I was looking for, but I do need the row numbers to stay the same. It was doing this before when one was inserted to move the data down to make way for the next data then deleting a row below it? Do you know what I mean?
I don't know if you have time or not but 2 other things. One you may have noticed on the hidden rows the cell to the left of where the data's put, there's a cell with delete on it. I need this kept next to the inserted data?
The other thing is to the right of the whole thing starting on column Z the data is linked there. I need a macro to copy and move this to the meal and day planner sheet in the format that I've demonstrated there. I could do this if it was the same amount of data everytime but obviously it going to vary?

craigo · Answer

Hi, it's not compiling the list now, it's only leaving the data last placed there and the previous is disappearing. I'm not sure if the data is copying over the previous or whether that's the row that's being removed? One more thing on this, after data has been inserted is it possible to return the curser (active cell) to the B column of the data that was just inserted?
The data to the right, when a list is compiled it's linked there so I need the one row then whatever is in the list too. You'll notice that there's 20 lines which is the maximum in the list although the number of lines used each time will vary. In the meal & day planner I've done a mock up of what I'd like it to look like when copied over, hopefully you'll be able to understand from that. What I really wanted it to do was when a new list was saved to the meal and day planner for it to A - Z the list according to the food type but because of the lay out I don't think that it's possible, do you know if it is or not?

craigo · Answer

That's excellent, thanks, I must have been downloading the wrong one. Now to try and explain the meals and day planner copy and paste. On the meal counter tab, imagine that there's four things in the list so if you scroll to the data on the right they'll be (or will be) showing up in that list. What I'd like to do is copy that data as a block, so in this instance it will be W31 across to AO31 and down to row 34. (There could be more or less in the list though so it has to know where the data ends) That would then be copied (Values & format) and pasted in the next available slot on the meal and day planner tab. I've manually put 3 examples on that tab (I know the select buttons a different colour) as they would hopefully look, each with a space between them. Also I would like to hide the data on the meal counter page so I'm taking it that it would need unhiding?

WutUp WutUp · Answer

OK, here is my first stab at it. Columns W thur AO are hidden. I deleted the rows on the Meals & Day Planner tab so you can see how the macro works. The data copied from the table will only be what is filled. Once it is pasted, it will be with formats and values. Also the formatting for the word "Select" will change as you requested. I have a command button on the Meal Counter sheet called Copy to M&D Planner. Click it to see the macro run. I put Rev1. at the beginning of the file name. This is just to make sure you get the updated copy.

http://www.4shared.com/file/91861691/9125752f/Rev1calorie_counter_hidden_row_macroplanner2.html

craigo · Answer

That's absolutely perfect, I'm so impressed, thank you. The only thing it needs is both sheets protecting everytime. 
Just a couple more questions, the list on the meal counter tab, I think I know how to get the delete buttons to work but I'm not sure how to get it to re hide the row after its deleted the data? (I know its not inserting the last column of data in the list either(%fat) but I know how to change that).

As I mentioned before I don't think its possible to get it to organise the data copied to meals & day planner in alphabetical order according to food type, but I just wanted to check this with you?

To copy the data into the day planner section I was going to put buttons in place of the numbered cells each with their own macro with an offset from the active cell. So you'd click the select button then you'd click the button on the line where you want to copy it to. Is this the best way to achieve this? 

If I wanted to move the button you've put on how do I do this, I thought you only had to right click them but it doesn't seem to work?

WutUp WutUp · Answer

1) The sheet protection is back in the code. I took it out while testing so it would not aggravate me.
2) I adjusted the code in the new revision so the %fat should show now. If you want to hide a row or column, set the hidden property to true or false.
Example:
rows("2:6").Hidden = True
rows("5:15").Hidden = False
3) I had to think outside the box on the sort, but when you run the updated spreadsheet, I think it will be sorted the way you want.
4) The buttons you want to add...Can you send another file on the 4shared site so I can see what you mean? It does sound ok the way you are stating though.
5) You will have to copy the code inside the button and create a new button, and then paste the code. If you just try to right click the button, it does not copy the code with it.
6) The updated file is now Rev2 at the beginning of the file name.

http://www.4shared.com/file/92038754/246bfc91/Rev2calorie_counter_hidden_row_macroplanner2.html

craigo · Answer

Ok that's great thanks. It'll be a day or so until I upload the revised version so I'll keep you posted and thanks again you've been fantastic, I really appreciate it!

craigo · Answer

Hello again. I've uploaded the latest version and just called it "calorie counter" The part I'm stuck on which I mistakenly thought I had the info for is how to offset from the active cell then copy for example the next 5 cells. As you'll see on meal and day planner my aim is to select the "select" cell then click the appropriate number button. The macro then should offset, from the active cell, one to the right and copy all data up to column M but all its doing is copying column M and none of the others. This is also the basis I want to use for the delete button. When a delete cell is clicked it offsets to the data and deletes it?
On the meal counter tab I've hidden column A which now contains the delete buttons. If you click add / remove foods the macro works but I also want it to unhide the delete buttons and hide the add buttons but because the add buttons are active I can't find a way to hide them. I suspect I'll have the same problem when the delete buttons are active?
To fit in my refresh page button I had to move the hidden data over. (you'll see I've just moved your button to the right for now). I've tried to change the coding to adjust for the move but I can't get it to work?

craigo · Answer

http://www.4shared.com/file/92390249/4c9a3166/calorie_counter.html

Here's the link

WutUp WutUp · Answer

I was just checking the sheet again, and I noticed the sort was not working when copying and pasting. It should be corrected now. Use this copy.


http://www.4shared.com/file/92418019/daa842db/Rev1calorie_counter.html

craigo · Answer

Thats great, I've got the buttons working spot on now. When I click to save meal (On my button now) it unhides my refresh button in columns w&X. Also I can't delete the button that you made?
Funnily enough I thought the delete buttons would be the easy bit but I'm struggling.
As there's so many delete buttons in different places what I'd like to try and do is similar to the selecting foods into the list. i.e. specify different ranges of cells (A28:A600) (B3:B22) and mdp tab (A2:A15) then offset and clear contents when either of those cells are selected. Except on the food list it will have to re hide the cells. Is this possible?
Here's the link. Rev5 now!

http://www.4shared.com/file/92553107/cc14c6ce/Rev5calorie_counter.html

craigo · Answer

Now I see that it was the design mode that I was missing for your button delete, sorted now thanks.
Its looking really good now, the deletes work perfectly.
Just a couple of teething problems, it's not unhiding my refresh button on the add foods macro and its not hiding all of the rows from 1:25? I have looked at the coding and I can't see why but then I'm only just starting to understand the basics. 
Also it's not re protecting the sheets for add foods or refresh?
When its in addfoods mode is it possible to use the same principle as the add food to list code for the delete button?
On the MDP tab I've put an example of the delete button for the saved meals and a note on the sheet (you may have to read it from the function pane) about how i'd like it to work, see what you think?

One more thing, I want to change the tab names to "Meal Planner" instead of meal counter and "Day Planner" instead of MDP. Will this cause any problems?

Here it is Rev 7

http://www.4shared.com/file/92760107/d4c9b2fb/Rev7calorie_counter.html

WutUp WutUp · Answer

I had a minute to work on the Refresh and Add Foods buttons. I think it is how you want it now.
Also, sheet protection should be set for the entire spreadsheet regardless of of the macro you are running.

Rev9
http://www.4shared.com/file/92966182/731bf2d0/Rev9calorie_counter.html

craigo · Answer

Wow that's fantastic, thank yon so much! As far as I can see everything is working fine. One thing that does happen though that I hadn't thought about is when there's things in the list and you go to add foods and back the things in the list get hidden?
What I mean by the delete button in addfoods mode is can we set a range for the delete command from A28:A600 like for the add button instead of having to list each one?
On the requirements tab there's an option for meals per day. Is it possible to have the day planner adjust to the figure thats in this box, probably by hiding the rows? E.g. if I were to put 4 meals a day the day planner would hide rows 11:15?
What does Dim mean in the code?

Get macro to detect and hide blank rows

36 responses

Similar discussions

Subscribe To Our Newsletter!