Run macro automatically after changing cell

[akuku]

Hi all!

Hope you can help me improve presented macro. Below you can see code which firstly unhides rows and then hides the blank ones. My question is, how can I run it automatically whenever let's say cell A1 changes?

Sub ShowAndHide ()
Range("A5:A10").EntireRow.Hidden = False
Dim cell As Variant
 
    For Each cell In Range("A5:A10")
    If cell.Value > "" Then
        cell.EntireRow.Hidden = False
     
    Else
        cell.EntireRow.Hidden = True
    End If
    Next cell
End Sub

Thanks in advance!

 

[Smallman]

Hi Akuku

Put the following in the worksheet object.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        [a5:a10].EntireRow.Hidden = False
        On Error Resume Next 'Just in Case all cells are filled
        [a5:a10].SpecialCells(4).EntireRow.Hidden = True
        On Error GoTo 0
    End If
End Sub

Do you need the first line? Will there be a possibility that you the cells from A5:A10 have changed since the last time the code was run you could probably get away without this line;

[a5:a10].EntireRow.Hidden = False

Take care

Smallman

 

[NARAYANK991]

Hi ,

Something like this ?

Private Sub Worksheet_Change(ByVal Target As Range)
            If Application.Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
            If Target.Count > 1 Then Exit Sub
            Application.EnableEvents = False
           
            If Target = vbNullString Then Call ShowRows(Range("A5:A10")) Else Call HideRows(Range("A5:A10"))
           
            Application.EnableEvents = True
End Sub
 
Sub ShowRows(Range_of_Rows)
    Range_of_Rows.EntireRow.Hidden = False
End Sub
 
Sub HideRows(Range_of_Rows)
    Dim cell As Range
 
    For Each cell In Range_of_Rows
        With cell
            .EntireRow.Hidden = (.Value = "")
        End With
    Next cell
End Sub

Narayan