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

  • Thread starter Thread starter DarrenL
  • Start date Start date
D

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
 
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
 
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
 
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
Click to expand...

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
 
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!
 
Back
Top