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


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
 
Ad

Advertisements

B

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
 
J

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
 
J

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
 
Ad

Advertisements

D

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top