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

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

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

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

"DarrenL" wrote:

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

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

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
---------------
Jacob Skaria


"DarrenL" wrote:

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

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

On Jun 11, 1:15*pm, DarrenL wrote:
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

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

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
Set rng = Me.Range("c18")
If Not Intersect(rng, Target) Is Nothing Then
Rows (19) .EntireRow.Hidden = IsEmpty(rng.Value)
End If

ANY MORE THOUGHTS?
Thanks so much BTW!
"Jennifer" wrote:

On Jun 11, 1:15 pm, DarrenL wrote:
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