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

36 replies

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
1
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.....
0
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 2, 2009 at 03:56 PM
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
0
OK, you can see if this is close. I Don't know if selected the correct rows, but we can modify once you try it.

Private Sub Macro1()

Dim j
Dim x
j = 2
x = 3

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

For x = x To 24

If Rows(x).Hidden = True Then
Rows(x).Hidden = False
End If
Next x

Rows("4:4").Select
Selection.EntireRow.Delete
Rows("4:24").Hidden = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub
0
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 3, 2009 at 11:28 AM
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
0
If you could provide a sample file that might make it easier. Anyway, lets back up a little because it appears I was not clear on a few steps. I provided comments by the code this time so maybe it will help. This time it is modified to just delete the next blank row, assuming that there is only one blank row from rows 3 thur 22. If this is correct, we will move to the next step.

Sub Macro1()

Dim j
Dim x
j = 2
x = 3

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

For x = x To 22 'x is equal to 3. So, start with row 3 and cycle thru the rows and stop when the counter reaches 22.

If Range("B" & x) = "" Then 'If column B contains an empty cell then delete the entire row (still cycling from x to 22)
Rows(x).EntireRow.Delete
End If

Next x

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub
0

Didn't find the answer you are looking for?

Ask a question
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 4, 2009 at 10:59 AM
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.
0
Here is a link to a file. It means nothing to you, but it will get you to the site.
You can set up a free account there. All you have to do to sign up is provide an email address and create a password.

http://www.4shared.com/file/90418748/420881a4/Color_Cells.html
0
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 5, 2009 at 10:48 AM
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!
0
What is the file called? Or, paste the link. Thanks for being patient!
0
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 6, 2009 at 03:22 PM
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.
0
OK, I believe this is what you want.
Please be aware that every time one of the hidden rows is deleted the row numbers move up one spot, but that will not cause a problem with the integrity of the data.

[URL=http://www.4shared.com/file/91546293/23abc4d8/calorie_counter_hidden_row_macroplanner2.html]calorie_counter_hidden_row_macroplanner2.xlsm[/URL]


Let me know how that works for you.
0
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 7, 2009 at 04:03 AM
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?
0
OK, try it now. If you look at the code, I added comments so you can follow along with the logic. New selections will be added to the bottom of the list instead of the top. It was easier to code that way so the whole data set would not have to be moved.
As far as the data to the right. Do you need the one row plus the data table copied?
Where exactly does it need to be pasted in the Meals & Day Planner tab?


http://www.4shared.com/file/91660925/fc5629d5/calorie_counter_hidden_row_macroplanner2.html
0
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 8, 2009 at 09:59 AM
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?
0
Hi, I downloaded the file again from the site to make sure I didn't change a row or something while I was testing.
I copied the code in the file I pasted from the previous post to your original file, and I am not getting the problem you are referring to. Also, I modified the code to move the cursor to the row just inserted. I thought you meant move the cursor to the last selection in the range from A28 to A600. I deleted the file from the link I pasted in the last post. Here is the new file that is working for me. I don't know if you are copying and pasting the macro to a different file, but run the macro from the file I am providing if you are not already doing that. Please let me know if it is correct from the file I am sending back. I'm still trying to comprehend that last part of how you want the copy and paste to look in the meals & day planner tab.


http://www.4shared.com/file/91784667/86663053/calorie_counter_hidden_row_macroplanner2.html
0
WutUp WutUp > WutUp WutUp
Mar 8, 2009 at 01:41 PM
Sorry, I had the file open while I was uploading. Here it is.

http://www.4shared.com/file/91786689/5554f851/calorie_counter_hidden_row_macroplanner2.html
0
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 8, 2009 at 04:13 PM
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?
0
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
0
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 9, 2009 at 02:28 PM
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?
0
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
0
I forgot to mention....If you look at the formulas in the Meal Counter tab in the data table that is copied in pasted;
Columns AM, AN, and AO have VLOOKUP formulas instead of just setting the cells to "=". I had to do that to perform the sort function you wanted.
0
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 10, 2009 at 01:53 PM
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!
0
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?
0
http://www.4shared.com/file/92390249/4c9a3166/calorie_counter.html

Here's the link
0
1) Here is the code I suggest for copying the row from the Activecell for the first button.
You will notice that the first cell in the copy range is C2, then it skips to F2. This is because cells C2, D2, and E2 are merged cells. If you click on that cell and look in the Name Box, you see the cell reference is C2. This is also true for the Offset for the Activecell. You see in the code, it skips from 1 to 4. So, when you code the macro for the second button, just change the range for the cell references to C3, F3, G3, etc. The code for the third button will be C4, F4, G4, etc......
This will also just copy the cell contents so the formatting will not change as opposed to copy and paste.
I coded the first button for you. The contents of the row are empty. Click on the button to make sure that is how you want it.
Sub mdp_1()

ActiveSheet.Unprotect

Range("C2") = ActiveCell.Offset(0, 1)
Range("F2") = ActiveCell.Offset(0, 4)
Range("G2") = ActiveCell.Offset(0, 5)
Range("H2") = ActiveCell.Offset(0, 6)
Range("I2") = ActiveCell.Offset(0, 7)
Range("J2") = ActiveCell.Offset(0, 8)
Range("K2") = ActiveCell.Offset(0, 9)
Range("L2") = ActiveCell.Offset(0, 10)
Range("M2") = ActiveCell.Offset(0, 11)

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

2) When you code the delete buttons(or cells), I would use the ClearContents method.
Example: When you code the first delete cell it would be: Range("C2:M2").ClearContents
This also will just clear the contents of the cells without affecting the formatting on the sheet.

3) In the Meal Counter sheet, I added the Hidden Property to True/False for columns A and B. Click on your Add/Remove Foods button to see if that is ok.

4) I think I have everything adjusted correctly to fit the changes you made to the sheet. Check it out.
"Talk" (or write) to you soon. Here is the adjusted file. Rev.1 is at the beginning of the file name.

http://www.4shared.com/file/92415991/e111a3b3/Rev1calorie_counter.html
0
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
0
I adjusted the hidden columns so the refresh button would not show. See if that is ok.

I left the button on the sheet, and I will let you delete it for two reasons. First, just so you know how to do
it in the future. Second, you will need to put the sheet in design mode once you start working with the delete in
column A, or you will have a problem with the macro running when you try to unhide column A because of the
worksheet selection change event.
So, to delete the command button, click on the Developer tab, click on the Design Mode icon, put the cursor
over the button and it will change to a plus sign with arrows on all four ends. Click once on the button and
hit the delete key on your keyboard. Once again, when you start working with column A, click on the Design Mode
icon so the macro doesn't run.

Yes, the delete buttons were tricky because a few things had to happen for the whole workbook to run all macros
correctly. For example, setting the range for the delete MDP sheet so you would not get a runtime error when copying and pasting from the Meal Counter sheet. So, I went ahead and coded MDP sheet for the delete. See if that is ok. I also coded the delete for the Meal Counter sheet because I had to modify the worksheet selection change code to get two different ranges to work. I'm sure you know how to look at the code to see what I did, but if not, let me know.
As you delete the contents of the row in the Meal Counter sheet it will also rehide the row and the cursor will select the next cell up. See if that is ok too. Now, for the delete in the Meal Counter sheet, please clarify what cells need to be deleted. Once you give me that info., I might have to think about it for a little while as we will have to code for three different ranges to work together on the click event.

I modified the code a little for your refresh so it should run slightly faster.

Here you go. Rev.6
http://www.4shared.com/file/92611670/c163cfca/Rev6calorie_counter.html
0
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
0
Alright, I'm back. It's the weekend. Doing the family thing.

For the Refresh and Add foods buttons, can you tell me all rows and columns that you need hidden for each?
That way, when either button is clicked, the buttons will be displayed or hidden the way you want.

I'll get back to the protection, that should just be a slight adjustment.

Can you please clarify this question:
When its in addfoods mode is it possible to use the same principle as the add food to list code for the delete button?


I changed the sheet names to the new names that you want, because it is important or the code would get
an error when trying to reference the sheet name.

The delete buttons for the saved meals are now coded. Check it out. I think you will be pleased.

Rev.8 now.
http://www.4shared.com/file/92952627/341315d7/Rev8calorie_counter.html
0
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
0
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?
0
I inserted another tab in the workbook and named it Summary. This is so I would not have a huge post here.

Rev.10
http://www.4shared.com/file/93061927/eb22dda8/Rev10calorie_counter.html
0