Hiding rows after the row number referenced in a specific cell

Rowddawg · Aug 7th 2013

I have a workbook that has lists in two seperate sections of columns (a-q and s-aa) and that can be sorted via different macros to list different amounts in each column. I want to hide all results that are sorted that end up at the bottom of the list after the sort. When it sorts, all applicable info is listed at the top set of rows, there are 0's in between (hidden to look blank), and then whatever didn't hit the top of the list is sorted to the bottom.

I have a cell that lists which is the last row that contains usable info based on the sort at AI4.

How do I set up a macro to hide all rows after whatever row number shows up at the AI4?

At the end of the day I want to add this to each of the macros that are used to sort the lists, so it automatically only shows relevant data.

Thank you for any help.

holycow · Aug 8th 2013

Re: Hiding rows after the row number referenced in a specific cell

Code

Rows(Range("AI4") & ":" & Rows.Count).EntireRow.Hidden = True

Rowddawg · Aug 8th 2013

Re: Hiding rows after the row number referenced in a specific cell

Works like a charm. Thank you so very much.

Rowddawg · Aug 8th 2013

Re: Hiding rows after the row number referenced in a specific cell

It did until I tried to lock the sheet. It stopped working as soon as I did. Very new to VB, so not sure how to fix the issue. Worksheet is for someone else, and I don't want them to inadvertantly screw it up. Will look on forums as well, pretty sure this would be posted somewhere.

Rowddawg · Aug 8th 2013

Re: Hiding rows after the row number referenced in a specific cell

Found the answer in these wonderful forums. Thanks again!

holycow · Aug 9th 2013

Re: Hiding rows after the row number referenced in a specific cell

Hi Rowddawg

Something I neglected to mention in Post #2 is that helper cells should be used as a last resort and Cell AI4 sounds very much like a helper cell. If you can explain what is in this cell we should try to eliminate the helper cell by using a more efficient code.

Rowddawg · Aug 9th 2013

Re: Hiding rows after the row number referenced in a specific cell

The worksheet sorts two separate lists, and I use a combination of cell, and vlookup to return what row is the last one used out of both lists. That result is listed in AI4. Anything I can do to clean it up or be more efficient I would love to know. I've basically self taught myself excel and am learning a great deal by watching these forums. Specifically, here's the code for one row, and it's copied down the list to row 300. AG4 =IF(AND(A4=0,T4=0),1,0) AH4 =CELL("row",AG4) AI4 =IF(VLOOKUP(1,AG4:AH300,2,FALSE)<38,38,(VLOOKUP(1,AG4:AH300,2,FALSE))) I set the number to be 38 at the smallest so an entire page fits normally and the rest is cut off when the results are only a few rows. Thanks for any help, and for the help already given. I can link the sheet if that's preferred.

holycow · Aug 9th 2013

Re: Hiding rows after the row number referenced in a specific cell

Quote

I can link the sheet if that's preferred

Yes please

Rowddawg · Aug 9th 2013

Re: Hiding rows after the row number referenced in a specific cell

https://www.dropbox.com/s/3bg0…%20Master%20revised2.xlsm

Not sure about the best way to link this. It's too large to add as an attachement. First two sheets can have info dropped in them, 3rd sheet pulls info from both and is hidden. 4th sheet 'data' is where they view/sort results.

The sheet is normally protected, and each macro unlocks/locks after use. I've changed the password to blank, but you'll still have to unlock the sheet each time you press one of the buttons if you need to.

The data sheet is what is referenced in the cells from my previous response.

holycow · Aug 10th 2013

Re: Hiding rows after the row number referenced in a specific cell

Hi Rowddawg

I found more helper cells there than I am game to tackle so I just tackled the one of Cell AI4.

Code

Sub Sort1()


' Sorts all data twice to put needed items top of list


    Dim r As Long 'declare variable to determine first row to be hidden
    r = Columns("AJ").Find(What:="hide", LookIn:=xlValues).Row 'set row number of first row to be hidden
    ActiveSheet.Unprotect "director"
    Rows(4 & ":" & Rows.Count).EntireRow.Hidden = False 'changed start row number reference
    Range("A4:O" & Range("A4").End(xlDown).Row).Sort Columns(1), xlAscending 'more efficient sort code than recorder
    Rows(r & ":" & Rows.Count).EntireRow.Hidden = True
    ActiveSheet.Protect "director"


End Sub

Display More

Rowddawg · Aug 10th 2013

Re: Hiding rows after the row number referenced in a specific cell

Thank you. Wanted to make sure I understand clearly, like I noted earlier i'm self taught. By helper cell you mean I broke downt he formula and left it in multiple cells instead of putting everything back into one cell, correct? IF that's the case then absolutely, there are quite a few there. I need to get in the habit of combining everything into one cell, which I'm sure would lessen the columns I use.

Thank you again.

holycow · Aug 10th 2013

Re: Hiding rows after the row number referenced in a specific cell

Where there's a will there's a way and you have found a way to do everything. It's all very well done actually and you've done an excellent job teaching yourself.

Hiding rows after the row number referenced in a specific cell

Participate now!

Share

Users Viewing This Thread