hide blank rows

Hi,

I have this document all set up where data is put into the numbered tabs and it feeds into the continuum page. Users can then alter the values in N & Q to hide the relevant sections. There is a macro that hides all the blank rows.

I have started to add a section at the bottom based on some different values however I can't work out how to alter the macro to also hide the blank rows in this section.

If the values are equal to any of these B3.3, B3.2, B3.1, B2.3, B2.2, B2.1, B1.3, B1.2, B1.1, A2.3, A2.2, A2.1, A1.3, A1.2, A1.1 then it will put the name and code in the one cell but I need the blank rows hidden. I don't necessarily need these sectioned off like the sections above. It would be better if they end up looking like below. So maybe I need to change my formula and then add another macro to hide the blank rows in this section?

Jenny Black B3.3
Simon Green B1.2
Mary Yellow A2.2

Hopefully someone can make sense of that!!

Any help would be greatly appreciated.

It's tough to tell exactly what you're looking for because the formatting for the portion you're adding appears incomplete, but I think the crux of the issue you're running into is that the original HideBlank() procedure looks in column H to find the last row because that's where your sum/student count is located (H707). The procedure then operates from that row on up. In your sample, the procedure is still finding the last used row in H, so it is still only operating from row 707 on up. If you end up formatting your new sections and putting a sum/count at the (new) bottom in column H (H1262 or so), then the HideBlank() procedure should work as expected on your new section.

Thanks for the reply. That makes sense. I didn't want to go to the effort of completing it to find it wasn't going to work so I just added a sample. I really just want any student that has a score that starts with A or B to be added to the bottom along with their score as they are on a different scoring scale. I'll have a go at adding more to it and see how it turns out. I might get back to you once that is done if I run into trouble. It is more being able to understand the code you have written that might be my problem as I'm still learning about the coding process. Thanks again for all your help!!

Glad I could help! If you want to give it a quick test, put some text down in H1262, change N2 to one of the new minimum scores, and hit 'Sort'. It appears to run as expected for me.

I understand what you mean but the additional scores down the bottom would be different and would be an add on. The current section would operate as normal by hiding rows based on N1, N2 & N3 and the new section at the bottom would just hide the blank cells. The scores don't really need to be there in column A for the new part as they are in the cell with the name.I've added an example sheet so you can see what it would look like.

If you don't need the rigid categorization at the bottom, a few tweaks in the formula might help. In the attached, I changed the formulas from row 707 of Continuum on down to the form:

=IFERROR(TRIM(INDEX('1 EAL'!$C$5:$C$41,SMALL(IF(NOT(ISNUMBER('1 EAL'!$B$5:$B$41)),ROW('1 EAL'!$B$5:$B$41)-4),ROW(1:1)))),"")

Still array-entered, of course. This will clump all of the non-numeric scores at the bottom in the 'EAL' section. "Beth Mary" looks a little out of place because she's out of place on the original class list. Moving her to row 26 on sheet '1' will make 'Continuum' look cleaner. Regardless, modifying the HideBlank() procedure to the code below appears to do the trick:

Please Login or Register  to view this content.

I also stretched the range on your showall() procedure to account for the new data at the bottom. Give it a look, see if it's an improvement.

That is very close! The problem with this is that the F & F.5 scores are a part of the top scoring system but they are showing up in the bottom. It is only those scores that contain A or B that need to be down the bottom.

The bunching of names/hiding works well. Ideally I would prefer all the students in column B for example to be ordered B3.3, B3.2, B3.1, B2.3, B2.2, B2.1, B1.3, B1.2, B1.1, A2.3, A2.2, A2.1, A1.3, A1.2, A1.1 but that isn't a huge problem.

Getting the letters to order properly in the bottom section is a bit tricky. I think that the most manageable way to do it is to add a helper column to your hidden EAL sheets to help re-sort the relevant names. Doing that allows everything to continue updating automatically, which should make the workbook easy to adjust/update going forward. The helper columns take the form of:

=IF(SUMPRODUCT(--(LEFT(B5,1)<"C"),--(LEFT(B5,1)>="A")),SUBSTITUTE(SUBSTITUTE(B5,"A",10),"B",20)+ROW()*0.001,"")

...in D5 of each EAL page, then filled down. It basically quantifies the letters so they sort the way you want them to. It will also weed out the F and F.5 entries. You can then use the following formula in B707 of 'Continuum', adjusted and spread through G707, then filled down:

(array entered) =IFERROR(INDEX('1 EAL'!$C$5:$C$41,MATCH(IF('1 EAL'!$D$5:$D$41>100,LARGE('1 EAL'!$D$5:$D$41,ROW(1:1))),'1 EAL'!$D$5:$D$41,0)),"")

The VBA part can stay as it was in my previous post. See the attachment for a better look at things.

I'll admit that I'm not at my sharpest today (it was a very long night for Americans), so there might be a more efficient solution that I'm overlooking, but this approach appears to work without overcomplicating things.

Amazing!! Thank you very much!! That is exactly what I was after. Much appreciated!

Hi I have one little problem. It all seems to sort correctly but if a 0 value is entered and is listed as the lowest score and then sorted, B707 seems to hide and therefore hides one student. You can see in the attached example that when you press sort Jim Black disappears.

Sorry! Oversight on my part. Try changing the following line in HideBlank():

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

Perfect. No problem.I just wish I could read it and understand it. Thanks again for all your help.

My pleasure, good luck!