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~

 

[Peter_SSs]

acaPAWN7

As I understand it, on the 'LD' sheet you are trying to hide/show various rows from 17:21 depending on the number (from 0 to 5) in cell F19 on 'Main' sheet and to sum only the visible rows after the code has been run. Suppost the values on 'LD' sheet to be summed are in column D, then try this formula for the sum:
=SUM(D17:INDEX(D17:D21,Main!F19))

You may have to alter 'Main' if this is not the name of the sheet that the F19 Worksheet_Change activates from and you may have to change the column reference from D, depending on what column your 'LD' data to be added is in.

 

[acaPAWN7]

This formula will sum up the value in column.

=SUM(D17:INDEX(D17:D21,Main!F19))

If I want to apply also for the value along the rows, for example from row E7 till K7, then the code should be like this: =SUM(E7:INDEX(E7:K7,Main!F19)). Right?

I found out that IRR formula aren't working. It's because the years of the hidden value are still counted. If I delete the hidden cells, the IRR formulas is working after comparing with few examples and testings. But deleting cells will caused another problem, where the range of hide/show column in the P&L sheet will changed (but the same range has been set in the macro code in Main sheet).

Can IRR only calculate the visible cells (column)? I have tried the code below, but it's not working as I wanted to:

=+IRR(G17:INDEX(G22:G36,Main!F26))

There are two parts of hide/show column; G17-G21 and G22-G36. G17-G21 will be corresponded to Main!F19, and G22-G36 will correspond to Main!F26. The IRR cell is located at P&L sheet: F42. When the columns are shifted upward, the IRR calculates the values from G17 till G36. Between these range, there are hidden cells. But the IRR supposedly to calculate the visible column only. I don't want to use the delete method as it will jumble up all formulas and caused problems.

 

[acaPAWN7]

I hope someone could solve my problem. I noticed that the show/hide method doesn't work if the file is protected. Is there any way to overcome this problem also?

 

[Peter_SSs]

I hope someone could solve my problem. I noticed that the show/hide method doesn't work if the file is protected. Is there any way to overcome this problem also?

The problem started out with hide/unhide rows based on a cell value. It has since grown to include columns, multiple sheets, IRR function, Protection etc. The level of complexity has got to where I am afraid that this problem is getting beyond my capabilities.

One of the problems is I don't think we know enough of EXCATLY what is in your sheets and where, but I hope somebody may be able to help you get moving forward again.

 

[acaPAWN7]

Yup, everything starts to give complex problems. I think it can be solved if the IRR problem can be fix. Can the IRR formula calculates the visible cells only?

And I just thought of this idea few minutes ago. Can I apply the 'delete all hidden cells' method in your previous hide-cell code? Means any hidden rows or columns will be deleted permanently. Maybe applying the delete code after the show/hide code, somewhere within these lines:

ShowRows = Target.Value
Worksheets("NPV&IRR").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

By doing this way, the IRR problem will be fixed too. Sorry for causing too much trouble.

 

[Peter_SSs]

I don't believe IRR can directly be made to apply to visible cells only.

To delete rows instead of hiding them, change a line like this

ActiveSheet.Rows(ShowRows + 17 & ":21").EntireRow.Hidden = True

to this

ActiveSheet.Rows(ShowRows + 17 & ":21").EntireRow.Delete

However, won't this make a problem if F19 on 'Main' sheet is later changed to a larger number? The code I suggested earlier would now be trying to unhide rows that no longer even exist! Still you can try it (make sure you have a backup).

 

[acaPAWN7]

I have tried the above code. But after it's activated (ran), I found out all formulas at other sheets seem so messed up. All are filled with #VALUE!, because some calculations cannot run and it continue to effect all others. As my application contains many formulas that will link to each other, a single problem can involved to other sheets.


Just thinking of a new idea. Possibly, adding another code for the 'delete hidden cells' method will solve this problem (perhaps). Instead of putting the ActiveSheet.Rows(ShowRows + 17 & ":21").EntireRow.Delete to delete the hidden row, I suggest the delete code runs after the hide code (Worksheet_Change) is executed. Maybe this 'delete code' can be assign as a button (e.g. "Calculate IRR"), so the user will click it and the correct IRR will be displayed.


As for the summary for my solution:

1. First, the row will be hidden corresponds to the drop-down list from the user (Worksheet_Change).

2. User will fill in the related values (cashflow etc..)

3. The IRR runs and calculated, including the hidden rows. So the wrong IRR value will display.

4. Then, user must click the "Calculate IRR" button which contains the macro code for deleting all hidden rows.

5. Lastly, the correct IRR value will replace the previous one.


By the way, can the hidden cell to be assigned to a variable, then delete that variable?

Hope this can be a solution for my problems.

 

[acaPAWN7]

I just found out this 'delete hidden rows' macro and modified it to suit with my Excel.

Sub DeleteHideRows()
Application.ScreenUpdating = False
Set thws = ActiveSheet
Dim i As Integer
For i = Cells.SpecialCells(11).Row To 1 Step -1
With Rows(i).EntireRow
If .Hidden = True Then .Delete (xlShiftUp)
thws.Activate
End With
Next
Application.ScreenUpdating = True
thws.Activate
End Sub

Anyway, I don't know what For i = Cells.SpecialCells(11).Row To 1 Step -1 refers to. Maybe it refers to some specific range number (11) or else. Hope anyone could explain to me if I need to change the number. This code seems to work for the moment.