Hey All,
Back again looking for some help.
I recorded a macro which sorts data based on three columns. When it is run row 8 always seems to have its height set to 0. I've looked at the code and i cant see what is causing it.
I have attached a sample worksheet. Just to note the .Header was originally xlYes.
Hopefully you might see something I am not.
Thanks.
John
I replicated your problem but I don't know why that row height is going to 0. It is not hidden, it is a 0 height.
I see a couple of things in your code you should change. First, there is no need to do that initial Select. When I remove that, the problem on row 8 goes away. (I don't know why it would cause it, but appears to be related to the problem.)
Second, your key range doesn't match the sort range. Your keys are in rows 5-1000 but your sort range is 4-1000. You should make those match.
Thanks for your reply Jeff.
I tried your suggestion but it doesn't fix the issue. it only appears to happen when the macro is first ran. Also its not only row 8. If nothing was under heading 1 or heading 2 but was under heading 3 then the first row under heading 3 will be set to 0.
I've worked around this by setting all rows from 5:1000 to height 15 at the end of the macro. I am hoping this is only a short term fix until i can figure out what the issue is.
John
You probably should include the hidden column A on the sort range too so that its codes remain with their respective records.
Unfortunately, this did not solve the problem. Sigh.
Please Login or Register to view this content.
I also quickly checked the custom format rules you have but they seem ok.
It's a mystery still, and a challenge.
If this has been helpful, please click on the star at the left.
I was just playing with the file a bit and copied the entire used range from the "JAN" sheet to Sheet1 and ran the macro on Sheet1. The row height for row 8 remained unchanged. Strange!!!!!
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
I tried copy the entire row 8 to row 9, the I deleted the original row 8 and ran the macro. The "new" row 8 kept the original height.
Alf
Thanks for your insights all.
Never thought of that StuCram, I must update that.
Mumps1, I have this macro linked to a button on 12 identicle tabs (1 for each month). The issue occurs on each one. Do you think it could be something to do with the layout of the sheets?
Alf, it only appears to happen the first time the macro is ran on a sheet. which confuses the hell out of me because if it was an issue with the code surely it should happen every time the macro is ran.
I'm not sure I made my point clear. You copied the entire range on the "Jan" sheet to sheet1 and ran the macro on sheet1 with no change of row height for row 8.
I only copied the row 8 to row 9, deleted "original" row 8 so row 9 becomes the "new" row 8 and then ran the macro seeing no change of height of "new" row 8
As you say no problem with macro but could there be something strange with the original row 8?
Alf
More clues & perhaps a solution...
I recalled that merged cells can play havoc with sorting. So….
1. I selected all the cells and unmerged all groups of cells on the sheet. Then the sort macro worked as expected with row 8 remaining visible.
You probably want the merged cels in the top 3 rows to remain as they were.
2. Next, I tried unmerging all cells from row 4 row 1000, leaving the top 3 rows as they were since they are not part of the sort area.
And YAY - the sort macro again worked as expected.
Unfortunately, I was unable to find any cells in the sort area that were merged so there still is a bit of a mystery wrt to the specific cause of your problem.
Hope this helps
Last edited by StuCram; 09-03-2016 at 08:27 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks