Auto select next cell

[meangreen]

I have a macro that unhides the next row when I enter someting into the row above. I need help with my code to get it to automatically select the cell in column C of the newly unhidden row. I will need this to happen each time a new row is unhidden (up to 50 times). Here's my 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

 

[erik.van.geit]

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.

you spoke too soon
well, it CAN be done
take your time to choose a nice avatar

from the beginning I thought that your code was too complex for the purpose and I was playing with the idea of using another event ... let's see how to get there: thanks to your last reply things are more clear to me (perhaps someone else would have been quicker understanding what you need )

is this a good summary ?
ASSUMPTIONS:
1. cells C25:C75 will be changed (manual input) in chronological order, so first 25, then 26 ... till 75 (30 couldn't be filled before 29)
2a. only single cells will be changed at a time: if mutiple changes the code doens't have to run
or
2b. only changes in one row at a time are allowed (example: paste in A28:H28)
EVENT:
if some cell in C25:C75 is changed then the next row appears (unhidden)and the cell "nextrow, C" will get selected
EXAMPLE:
Cells C25 to C29 do have values, rows 25:29 are already visible
Cell C30 gets input
row 31 is unhidden & C31 selected

same story for changes in the other range(s)

correct ?

 

[meangreen]

Exactly.

 

[erik.van.geit]

2a or 2b ?

(prepare your avatar )

 

[meangreen]

2a I guess. The "C" cell will be the only cell changed in each row.

 

[erik.van.geit]

assuming these rows are hidden

Sub hide_rows()
Rows("26:75").EntireRow.Hidden = True
Rows("89:138").EntireRow.Hidden = True
End Sub

so to start 25 and 88 are visible

this code is a little overkill, but I can imagine you will extend it to more ranges (anyway it's way shorter than the original code you displayed)
then you will need to change the "nr"line and add some lines to define the ranges

Private Sub Worksheet_Change(ByVal Target As Range)
'Erik Van Geit
'070227

Dim rng() As Range
Dim i As Integer
Dim nr As Integer   'number of ranges
Dim flag As Boolean

nr = 2
ReDim rng(nr) As Range

Set rng(1) = Range("C25:C75")
Set rng(2) = Range("C88:C138")

If Target.Count > 1 Then Exit Sub

    For i = 1 To nr
    If Not Intersect(Target, rng(i)) Is Nothing Then
    flag = True
    Exit For
    End If
    Next i
    
    If flag = False Then Exit Sub
    
    Rows(Target.Row + 1).Hidden = False
    Cells(Target.Row + 1, "C").Select
    
End Sub

going to another keyboard now (rehearse piano)

when coming back, in a few hours perhaps, I hope to see a nice avatar and a little reply at "invitation ... DRAFT"
but even when not, it's a pleasure

greetings from Belgium,
Erik

 

[meangreen]

Wow! That's amazing. However, I just have one more minor detail I would like to see. Is there a way to ensure that all the blank rows remain hidden. Here's my thinking: Let's say I have added data from C25:C34, but then realize I don't want to keep the data in C30:C34. If I delete the data, 30:34 still remain visible. Can we make the rows automatically hide if blank. I know I can manually hide the blank rows, but I am creating this sheet for others so I would like it ot be automated as possible (and by automated, I mean 'idiot-proof').

I went ahead added my avatar since you did solve my problem. I just wish I wasn't wearing a pink shirt.....it's the only pic I could find while I'm at work.

 

[erik.van.geit]

the pink shirt is no problem what counts is that it is REAL !
you can always change your avatar (I changed mine yesterday)

If I delete the data, 30:34 still remain visible. Can we make the rows automatically hide if blank. I know I can manually hide the blank rows, but I am creating this sheet for others so I would like it ot be automated as possible (and by automated, I mean 'idiot-proof').

would you allow to delete 30:34 if 35 is already filled-up ?

 

[meangreen]

I would rather not delete any rows if possible, but if necessary I can adapt. I have some data in coulmn IV that would have to moved if rows will be deleted.

 

[erik.van.geit]

sorry, I mean delete the "data"
(that's how you wrote it)

so: can the contents of row 34 be cleared if there are data in row 35 ?
(it wouldn't be great trouble if it was allowed, but it wouldn't be 100% logic combined with the other code)

 

[meangreen]

Yes, ideally I would like to be able to clear the data in in row 34 even if there is data row 35.