Display Specified Number of Rows per Values in Column A

[MarqyMarq]

I am using Excel 2010. My son has a spreadsheet that has several hundreds of rows of titles of artist, songs, song length, rating, and so forth. I want to limit the number of rows to display for each artist, based on their rating. Let’s say, either 5 rows per artist, 10 rows per artist, or show all. The options (5, 7, all) to list would be in a CELL value at the top of my listing.

I think I can use an array, with Index and CountA, but I don’t know how to set it up. Any recommendations on how to start this?

3	<= Maximum rows to show
Artist	Song Title	Album	Rating
Artist 1	Song 1	Album A	5 Stars
Artist 1	Song 2	Album A	5 Stars
Artist 1	Song 5	Album B	4 Stars
Artist 2	Song 1	Album A	5 Stars
Artist 2	Song 2	Album C	5 Stars
Artist 2	Song 3	Album A	5 Stars
Artist 3	Song 1	Album C	5 Stars
Artist 3	Song 4	Album A	4 Stars
Artist 3	Song 5	Album C	3 Stars
Artist 4	Song 1	Album A	5 Stars
Artist 4	Song 2	Album B	4 Stars
Artist 4	Song 3	Album A	3 Stars
etc	etc	etc	etc

<tbody>
</tbody>

I’ve tried searching on the internet and tested code and have failed for hours. I have come to a complete stop and I just can’t find the solution to this problem.
Any help would be appreciated.

 

[WarPigl3t]

Sub myHide()
sht = "Sheet1"
rowsHideCell = "A1"
showNumberOfRows = Sheets(sht).Range(rowsHideCell).Value
firstRow = 3
lastRow = Sheets(sht).Range("A" & Rows.Count).End(xlUp).Row
Sheets(sht).Rows(firstRow & ":1048576").Hidden = False
r = firstRow
Do Until r > lastRow
rArtistName = Sheets(sht).Range("A" & r).Value
ArtistCounter = 0
rSearch = r
Do Until rSearch > lastRow
rSearchArtistName = Sheets(sht).Range("A" & rSearch).Value
If rArtistName = rSearchArtistName Then
If ArtistCounter > showNumberOfRows Then
Rows(rSearch).Hidden = True
End If
ArtistCounter = ArtistCounter + 1
End If
rSearch = rSearch + 1
Loop
r = r + 1
Loop
End Sub

 

[MarqyMarq]

Thanks WarPigl3t for the response. Unfortunately, when I plugged the code into my spreadsheet, nothing happened. Could it be that the variables were not declared?

Let me recap what I wish the code to do:

1) The options of "3", "5", "7", or "None" would be selected (data validation list) by my son. This value is placed in cell "A1".
2) Sort by Artist, and then by Rating. The info on rows 1 & 2 would NOT be hidden.
3) Starting on row 3, hide the excess rows (over 3, 5, or 7) based on Artist. NOTE: If "None" is selected, it would skip this process (If-then-else).
4) The result would be an Artist List with the top x# displayed in the spreadsheet. The spreadsheet name is "Summary".

So, I reviewed the code and added some variable declarations (sht) and (r). It still didn't work.

WarPigl3t, if you can review the code and see where I may have messed up, that would be great!

Thanks in advance!!!