create a loop so i don't have to keep retyping?

[DarrenL]

code is as follows, it's getting old retyping this over and over!!! can
anyone help with a loop for this? I just need to hide the row following a
cell the user types in if the cell is blank.....repeatedly.

Dim rng As Range
Set rng = Me.Range("c18")
If Not Intersect(rng, Target) Is Nothing Then
Rows(19).EntireRow.Hidden = IsEmpty(rng.Value)
End If

Dim rng2 As Range
Set rng2 = Me.Range("c19")
If Not Intersect(rng2, Target) Is Nothing Then
Rows(20).EntireRow.Hidden = IsEmpty(rng2.Value)
End If

Dim rng3 As Range
Set rng3 = Me.Range("c20")
If Not Intersect(rng3, Target) Is Nothing Then
Rows(21).EntireRow.Hidden = IsEmpty(rng3.Value)
End If

Dim rng4 As Range
Set rng4 = Me.Range("c21")
If Not Intersect(rng4, Target) Is Nothing Then
Rows(22).EntireRow.Hidden = IsEmpty(rng4.Value)
End If

 

[Barb Reinhardt]

I think you're looking for something like this

Option Explicit
Sub Test()

Dim rng As Range
Set rng = Me.Range("C18")

Do
If Not Intersect(rng, Target) Is Nothing Then
rng.Offset(1, 0).EntireRow.Hidden = IsEmpty(rng.Value)
End If
Set rng = rng.Offset(1, 0)
Loop While Not IsEmpty(rng)

End Sub

HTH,
Barb Reinhardt

 

[Jacob Skaria]

Try

Dim rng As Range
Set rng = Me.Range("c18:C21")
If Not Intersect(rng, Target) Is Nothing Then
Rows(Target.Row + 1).EntireRow.Hidden = IsEmpty(rng.Value)
End If


If this post helps click Yes

 

[Jennifer]

code is as follows, it's getting old retyping this over and over!!!  can
anyone help with a loop for this?  I just need to hide the row following a
cell the user types in if the cell is blank.....repeatedly.

    Dim rng As Range
    Set rng = Me.Range("c18")
    If Not Intersect(rng, Target) Is Nothing Then
      Rows(19).EntireRow.Hidden = IsEmpty(rng.Value)
    End If

     Dim rng2 As Range
    Set rng2 = Me.Range("c19")
    If Not Intersect(rng2, Target) Is Nothing Then
      Rows(20).EntireRow.Hidden = IsEmpty(rng2.Value)
    End If

     Dim rng3 As Range
    Set rng3 = Me.Range("c20")
    If Not Intersect(rng3, Target) Is Nothing Then
      Rows(21).EntireRow.Hidden = IsEmpty(rng3.Value)
    End If

    Dim rng4 As Range
    Set rng4 = Me.Range("c21")
    If Not Intersect(rng4, Target) Is Nothing Then
      Rows(22).EntireRow.Hidden = IsEmpty(rng4.Value)
    End If

Dim X as Integer

For X = 18 to 21

Dim rng3 As Range
Set rng3 = Me.Range("c" & X)
If Not Intersect(rng3, Target) Is Nothing Then
Rows(X).EntireRow.Hidden = IsEmpty(rng3.Value)
End If

Next X

 

[DarrenL]

Jennifer,
Thanks, yours was the only one that i could actually get to work. However,
the problem is that the row being hidden has to be dependant on whether the
row above it had anything written in column A. See my code below:
Dim rng As Range
ANY MORE THOUGHTS?
Thanks so much BTW!