On Jul 23, 5:55*am, "Rick Rothstein \(MVP - VB\)"
wrote:
Use this code instead; it keeps the "cursor" on the same row if that row is
68 (your last hidden row)...

Private Sub Worksheet_Change(ByVal Target As Range)
* On Error GoTo Whoops
* If Target.Column = 12 Then
* * If Len(Target.Value) 0 And Target.Row < 68 Then
* * * Target.Offset(1).EntireRow.Hidden = False
* * * If Target.Row < 68 Then Target.Offset(1).Select
* * Else
* * * Target.Select
* * End If
* End If
Whoops:
End Sub

Rick

"Rick Rothstein (MVP - VB)" wrote in
l...



I think the following Change event code does what you want (and can replace
all 50 of your If-Then blocks at the same time). Note... you used the
SelectChange event in your first post, but since in your last post you said
you wanted to react off the Enter key, I used a Change event procedure
instead.

Private Sub Worksheet_Change(ByVal Target As Range)
*On Error GoTo Whoops
*If Target.Column = 12 Then
* *If Len(Target.Value) 0 Then
* * *Target.Offset(1).EntireRow.Hidden = False
* * *If Target.Row < 68 Then Target.Offset(1).Select
* *Else
* * *Target.Select
* *End If
*End If
Whoops:
End Sub

I wasn't sure what to do if you erased an earlier entry, so I do nothing;
that means you can create a blank L:N merged cell in between filled in
cells.

Rick

wrote in message
....
On Jul 23, 1:49 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Your full layout is a little unclear to me. For example, do any of the
cells
in Column L of the hidden rows have values in them? Where did you want
the
"cursor" to be after the row is unhidden... on the row you just unhid? If
so, in which column? Based on what you posted so far, I can tell you that
your 50-some If-Then blocks can be replaced with a single If-Then; but
how
to handle it (or if doing so is even right) depends on what it is you are
ultimately trying to do. Can you give us more details on what you want to
do
(interaction-wise) and what you want to happen as a result?

Rick

wrote in message

....

Excel 2000

Hello

I've set up some code (crudely - I'm a VBA beginner) to make a row
unhide itself when a cell above that row has something typed into it
thus:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Stops showing every step on screen
Application.ScreenUpdating = False

If [L19].Value 0 Then
Range("A20:N20").EntireRow.Hidden = False
End If

If [L20].Value 0 Then
Range("A21:N21").EntireRow.Hidden = False
End If

If [L21].Value 0 Then
Range("A22:N22").EntireRow.Hidden = False
End If

etc down to row 69.

Trouble is, on activation, the row unhides itself ok but thecursor
ends up below the row that's just been unhidden, amongst the hidden
rows, and I want it IN the row that's just been unhidden, ie if I've
just typed something into L21 and Entered, I want thecursorto be in
L22 - ready for the next entry.- Hide quoted text -

- Show quoted text -

Hello Stumped. *Thanks for that, it's working so far - but I think I'm
going to see what Rick says - I sort of knew that there'd be a quicker
way than all those If-Thens.

Hello Rick. *Col A is the only column of A:N which has anything in it..
So for row 19, A19 has text already in it, B19:K19 (one merged cell)
is empty, L19:N19 (one merged cell) is empty, and so on down.

The idea behind this is only to show as many rows as there is data to
be entered (actually it'll turn out as number of rows needed + 1, but
that's ok), so I've got row 19 showing to start with as I know
there'll be at least one row needed, rows 20 to 68 (last row for
entering data) are hidden. *The assumption is that if L19:N19 is
filled then another row will be wanted, so I want the user to be able
to type data in L19:N19, Enter, and end up with row 20 Unhidden and
the 'cursor'/selected cell/sorry not sure of correct term, on L20:N20
ready for the next data entry, and so on. *At the moment, the 'cursor'
seems to be 'jumping down' one or more cells and disappearing into the
'clump' of hidden cells below row 20, I have to key 'up-arrow' to get
it back into L20:N20.

I hope that's clear.- Hide quoted text -

- Show quoted text -

Hello Rick

Neither of your programs works, I'm afraid. Perhaps I'm doing
something wrong, I did this:

Select (your code - I tried it with both sets of code)
Copy

Then in VBA window
Insert
Module
Paste