Input number of rows

[acaPAWN7]

I want to make a list of numbers which can determine the number of rows from the user. Let say, the list is between 'year 1' to 'year 5'. If the user choose 'year 3', the rows will be added from 'year 1' til 'year 3'. could this be made possible?

or the other way, possibly is there any method to hide the existing row from the user view? so if the user select 3, the 'year 1' til 'year 3' will be unhidden. and the remaining rows ('year 4' and 'year 5') will be hidden from the user view.

I'm not sure whether there are ways to solve this kind of problem. hope any expert can help me. thanks~

 

[acaPAWN7]

This code should be in the Worksheet_Change evnt for sheet "Main"

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ShowRows As Integer
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$D$19" Then Exit Sub
    ShowRows = Target.Value
    Worksheets("LD").Activate
    ActiveSheet.Rows("7:21").EntireRow.Hidden = False
    If ShowRows = 5 Or ShowRows = 0 Then Exit Sub
    ActiveSheet.Rows(ShowRows + 17 & ":21").EntireRow.Hidden = True
End Sub

You mean I need to put this code in the 'Main' sheet, right? Btw
If Target.Address <> "$D$19" Then Exit Sub <-- "$F$19",
ActiveSheet.Rows("7:21").EntireRow.Hidden = False <-- "17:21"..

That's just a typo, right? A big thank you for helping me to solve this problem..



About the columns that I said before, I just wanna ask you whether the same code (show/hide rows) can be applied in show/hide columns.. The drop-down will be at 'Main' sheet (F26), and the show/hide columns (column F - T) at 'P&L' sheet.. Can the code be like this (it's just my assumption)?:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ShowColumns As Integer
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$F$26" Then Exit Sub
    ShowColumns = Target.Value
    Worksheets("P&L").Activate
    ActiveSheet.Columns("F:T").EntireColumn.Hidden = False
    If ShowColumns = 15 Or ShowColumns = 0 Then Exit Sub
    Columns(ShowColumns + F & ":T").EntireColumn.Hidden = True
End Sub

Is it correct? I just came out from the previous "show/hide rows" code, where I replaced every 'ShowRows' into 'ShowColumns', 'EntireRow' to 'EntireColumn' n bla2.. For the drop-down at 'Main' sheet, I wanted to make it number of years as 15 years.. Hope you'll understand my explaination.. Sorry for the troublesome =)

 

[Peter_SSs]

You mean I need to put this code in the 'Main' sheet, right?

Yes

Btw
If Target.Address <> "$D$19" Then Exit Sub <-- "$F$19",
ActiveSheet.Rows("7:21").EntireRow.Hidden = False <-- "17:21"..

That's just a typo, right?

.Yes, again. Both my mistakes.

I have not tested the code you posted for hiding columns but it looks pretty much on the riight track. Why not give it a try? Then if it works you don't even have to ask. If not, you can describe what didn't work and ask for specific help about that.

 

[acaPAWN7]

I have pasted the code for show/hide columns, but it seems that it won't work.. After I select the number of years at the drop-down, the column F - T at P&L sheet are not changed..

For example, if I select 4 years, column F - I (F, G, H & I) should only be displayed and column J - T should be hidden.. But that's not working.. I think the codes for show/hide column are much different from rows, right?

Perhaps I wanted to set a range of rows that should be show/hide too.. Coz I don't want to affect the calculation at the same column below (from column F - T, rows > 44).. The rows are between 18 - 44..

And also there's an error whereby during I compiling the codes, an ambigious name detected: Worksheet_Change.. I need to changed it to other different name, is it?

Hope this problem can be solved =)

I put up with the table image in the P&L sheet.. The light blue color are the rows and columns that should be show/hide:

 

[Peter_SSs]

acaPAWN7

See if this code as the Worksheet_Change event for the 'Main' sheet is what you want. This hasn't addressed what I think is your desire to also hide rows on the P&L sheet but you may be getting the idea enough (if the code below is headed in the right direction) to modify this code yourself to add in another section.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> ShowRows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> ShowColumns <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> FirstHiddenCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Address <> "$F$19" And Target.Address <> "$F$26" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Address
        <SPAN style="color:#00007F">Case</SPAN> "$F$19"
            ShowRows = Target.Value
            Worksheets("LD").Activate
            ActiveSheet.Rows("17:21").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">If</SPAN> ShowRows = 5 <SPAN style="color:#00007F">Or</SPAN> ShowRows = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            ActiveSheet.Rows(ShowRows + 17 & ":21").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "$F$26"
            ShowColumns = Target.Value
            Worksheets("P&L").Activate
            ActiveSheet.Columns("F:T").EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">If</SPAN> ShowColumns = 15 <SPAN style="color:#00007F">Or</SPAN> ShowColumns = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            FirstHiddenCol = Chr(ShowColumns + 70)
            ActiveSheet.Columns(FirstHiddenCol & ":T").EntireColumn.Hidden = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[acaPAWN7]

Ok, I'll test it.. Thanks by the way..

Nway, just want to ask you about the code:

FirstHiddenCol = Chr(ShowColumns + 70)

What does it means by ShowColumns + 70? Can you explain to me?

In the P&L sheet, I want do some modification for the column F - T b'coz not all of the rows in column F - T that will be show/hide automatically.. I just want to set specific rows in column F - T will be hidden only (row 8 - 44).. Which means if I select the drop-down, row 45 and below will not be affected by show/hide of column F - T (there is a table starts at F49)..

Is it possible? If can't, I will put the 2nd table which is located under the 1st table (the show/hide column) at the right next to it (start at Y18).

 

[Peter_SSs]

FirstHiddenCol = Chr(ShowColumns + 70)

What does it means by ShowColumns + 70?

Columns are represented by letters: A, B, C etc These letters in turn are represented behind-the-scenes by numbers A=65, B=66, C=67 etc. Your first column is column F (represented by 70). So if you wanted to show 2 columns, the first hidden column would be column H(=72 or 70 + 2)

For the second part of your question, I think you are asking if you can only hide part of one or more columns in the range of columns F:T. If that is the question, then the answer is no.

 

[acaPAWN7]

For the second part of your question, I think you are asking if you can only hide part of one or more columns in the range of columns F:T. If that is the question, then the answer is no.

For correction, I'm not asking about the columns, it's "hide part of one or more rows in the range of columns F:T".. Hope you get what I mean here.. The affected show/hide rows are : row 8 - 44..

Anyway, Happy Halloween~

 

[Peter_SSs]

For correction, I'm not asking about the columns, it's "hide part of one or more rows in the range of columns F:T".. Hope you get what I mean here.. The affected show/hide rows are : row 8 - 44..

Anyway, Happy Halloween~

The only way to hide the part of any row(s) from column F to column T is to hide the whole of the columns F:T. That is, you cannot hide individual cells - just whole rows or whole columns.

 

[acaPAWN7]

Ok, now I'm understand.. Thanks for your help

 

[acaPAWN7]

Hye. Thanks for helping me in the 'Input number of rows' post. About your code of hiding-cell method, if I want to change the hide into delete cell method, how can it work? Your previous code was like this:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ShowRows As Integer
    Dim ShowColumns As Integer
    Dim FirstHiddenCol As String
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$F$19" And Target.Address <> "$F$26" Then Exit Sub
    Select Case Target.Address
        Case "$F$19"
            ShowRows = Target.Value
            Worksheets("LD").Activate
            ActiveSheet.Rows("17:21").EntireRow.Hidden = False
            If ShowRows = 5 Or ShowRows = 0 Then Exit Sub
            ActiveSheet.Rows(ShowRows + 17 & ":21").EntireRow.Hidden = True
        Case "$F$26"
            ShowColumns = Target.Value
            Worksheets("P&L").Activate
            ActiveSheet.Columns("F:T").EntireColumn.Hidden = False
            If ShowColumns = 15 Or ShowColumns = 0 Then Exit Sub
            FirstHiddenCol = Chr(ShowColumns + 70)
            ActiveSheet.Columns(FirstHiddenCol & ":T").EntireColumn.Hidden = True
    End Select
End Sub

If I want to change from hide rows/columns to delete rows/columns, do I need to change the code "Hidden" to "Delete" ? I tried it, and it won't work.

I found out the hide method can't update the formulas below it. For example, A1 till A10 are columns that will correspond to the drop down (cashflow according to years; eg year 1 till year 10). While A11 is the total of the overall cashflow (for 10 years). After I applied your code, seems the total function doesn't work.

Such example, I select 7 from the list of drop-down. So the columns that should be visibled are A1 till A7. A8 till A10 are hidden. But, the total of the overall cashflow is still sums up from A1 till A10. Means it cannot subtotal the visible columns only. How can I avoid this?

Hope you can help me to solve this problem