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]

experiment with this
it works for me - perhaps missed some possible "dumb-actions"

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng() As Range
Dim intersection 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")

    For i = 1 To nr
    Set intersection = Intersect(Target, rng(i))
    If Not intersection Is Nothing Then
    flag = True
    Exit For
    End If
    Next i
    
    If flag = False Then Exit Sub
    
    If Application.WorksheetFunction.CountA(Target) = 0 Then
    Target.EntireRow.Hidden = True
    Target(1).Offset(IIf(Target.Count = 1, -1, 0)).Select
    
    ElseIf Target.Count = 1 Then
    Rows(Target.Row + 1).Hidden = False
    Cells(Target.Row + 1, "C").Select

    End If
    
End Sub

BUT, allowing to delete some cells before other data, will get some trouble ... how to get that row visible again ??

so think about a bit and then define what actions should take place under specific circumstances ...
say you have data
C24
C25
C26
C27
(C28 hidden)
clearing C26 you get
C24
C25
C27
(C26 hidden)
you see the problem ?

 

[meangreen]

Ok, I see. I think it would be best to not allow to delete cells if there is data in the cells below. So, in your example C26 could not be cleared until C27 is cleared. Can this be done?

If it can be done, can I have an error message pop up when the user tries to delete data that can not be deleted?

 

[erik.van.geit]

this works for me
a lot of study to do for you

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng() As Range
Dim isect As Range   'intersection
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")

    For i = 1 To nr
    Set isect = Intersect(Target, rng(i))
    If Not isect Is Nothing Then
    flag = True
    Exit For
    End If
    Next i
    
    If flag = False Then Exit Sub
    
    If Application.WorksheetFunction.CountA(Target) = 0 Then
    
        If Application.WorksheetFunction.CountA(Intersect(isect.Resize(Rows.Count - isect.Row), rng(i))) = 0 Then
        isect.Offset(1).EntireRow.Hidden = True
        isect(1).Select
        Else
            With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
            MsgBox "You are not allowed ... ", 48, "ERROR"
            End With
        End If
    
    ElseIf Target.Count = 1 Then
    Rows(Target.Row + 1).Hidden = False
    Cells(Target.Row + 1, "C").Select

    End If
    
End Sub

 

[meangreen]

This is awesome! Sorry for the delay, but I have been out of the office for awhile. Thank you for all of your help.

 

[erik.van.geit]

you're WELCOME !!
I never go to Office