I'm still not sure you're understanding what I need, so I'll try to explain one more time. I'll walk you through each step of the process. The value in A24 is correct, it is actually the number of nonblank cells from C25:C75 +1. I added the +1 because I really want A24 to represent the number of visible rows from 25:75. Row 25 will always be visible. Once I add data into C25 and hit enter, A24 will change to 2 (to represent the 2 visible rows from 25:75). Here was my original code:
Code:
Private Sub Worksheet_Calculate()
Dim rng As Range
Set rng = Range("A24")
If rng.Value <> "" Then
Application.EnableEvents = False
Rows("25:75").EntireRow.Hidden = True
Rows("25:" & rng.Value + 24).Hidden = False
Application.EnableEvents = True
End If
End Sub
This works perfectly for unhiding the correct row - I have no problems with unhiding the rows. My problem is that after entering data into C25 and hitting Enter, for some reason C26 (which should be default) is not selected but C36 is. I didn't like this so, I changed the code to this:
Code:
Private Sub Worksheet_Calculate()
Dim rng As Range
Set rng = Range("A24")
If rng.Value <> "" Then
Application.EnableEvents = False
Rows("25:75").EntireRow.Hidden = True
Rows("25:" & rng.Value + 24).Hidden = False
Cells(24 + rng.Value, "C").Select
Application.EnableEvents = True
End If
End Sub
That worked perfectly. This is where it get's tricky. I want to do the exact same thing as above in rows 88:138. So I editied the code to this:
Code:
Private Sub Worksheet_Calculate()
Dim rng As Range
Set rng = Range("A24")
If rng.Value <> "" Then
Application.EnableEvents = False
Rows("25:75").EntireRow.Hidden = True
Rows("25:" & rng.Value + 24).Hidden = False
Cells(24 + rng.Value, "C").Select
Application.EnableEvents = True
End If
Set rng2 = Range("A87")
If rng2.Value <> "" Then
Application.EnableEvents = False
Rows("88:138").EntireRow.Hidden = True
Rows("88:" & rng2.Value + 87).Hidden = False
Cells(87 + rng2.Value, "C").Select
Application.EnableEvents = True
End If
End Sub
At first I thought this worked perfectly as well. I tested everything in the range of 88:138. The rows unhid as they should have, and the correct cells were being selected. However when I went back up to the 25:75 ranges and added data, this part of the code was no longer recognized:
Code:
Cells(24 + rng.Value, "C").Select
and this was
Code:
Cells(87 + rng2.Value, "C").Select
In other words rather than C26 being selected (after entering data into C25), C88 was being selected based on
Code:
Cells(87 + rng2.Value, "C").Select
Then to complicate things further, each time I click my spinner button the same piece of code is being activated
Code:
Cells(87 + rng2.Value, "C").Select
In this case, I don't want any specific cell to be selected after clicking the spinner button.
Basically it comes down to this, I need a way to be able to use
Code:
Cells(24 + rng.Value, "C").Select
AND
Code:
Cells(87 + rng2.Value, "C").Select
on the same sheet depending on which part of the sheet I am entering data. Then also, have the spinner button not recognize either if them.
I'm afraid I already know the answer to this (it can't be done), but if you can figure this out, I will be forever grateful and to show my appreciation I will become a member of DRAFT.
