VBA help.

imback2nite

imback2nite

Board Regular
Joined
Oct 30, 2004
Messages
211
Office Version
  1. 2003 or older
Platform
  1. Windows
I've written this below and it works . . . but it's ugly. I've been trying to learn 'looping' I guess you'd call it but I just don't get it. Is there a better way of writing this?
Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
    If IsEmpty(Range("B2").Value) = True Then
        Rows("3:3").EntireRow.Hidden = False
        Rows("4:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B3").Value) = True Then
        Rows("4:4").EntireRow.Hidden = False
        Rows("5:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B4").Value) = True Then
        Rows("5:5").EntireRow.Hidden = False
        Rows("6:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B5").Value) = True Then
        Rows("6:6").EntireRow.Hidden = False
        Rows("7:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B6").Value) = True Then
        Rows("7:7").EntireRow.Hidden = False
        Rows("8:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B7").Value) = True Then
        Rows("8:8").EntireRow.Hidden = False
        Rows("9:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B8").Value) = True Then
        Rows("9:9").EntireRow.Hidden = False
        Rows("10:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B9").Value) = True Then
        Rows("10:10").EntireRow.Hidden = False
        Rows("11:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B10").Value) = True Then
        Rows("11:11").EntireRow.Hidden = False
        Rows("12:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B11").Value) = True Then
        Rows("12:12").EntireRow.Hidden = False
        Rows("13:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B12").Value) = True Then
        Rows("13:13").EntireRow.Hidden = False
        Rows("14:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B13").Value) = True Then
        Rows("14:14").EntireRow.Hidden = False
        Rows("15:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B14").Value) = True Then
        Rows("15:15").EntireRow.Hidden = False
        Rows("16:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B15").Value) = True Then
        Rows("16:16").EntireRow.Hidden = False
        Rows("17:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B16").Value) = True Then
        Rows("17:17").EntireRow.Hidden = False
        Rows("18:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B17").Value) = True Then
        Rows("18:18").EntireRow.Hidden = False
        Rows("19:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B18").Value) = True Then
        Rows("19:19").EntireRow.Hidden = False
        Rows("20:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B19").Value) = True Then
        Rows("20:20").EntireRow.Hidden = False
        Rows("21:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B20").Value) = True Then
        Rows("21:21").EntireRow.Hidden = False
        Rows("22:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B21").Value) = True Then
        Rows("22:22").EntireRow.Hidden = False
        Rows("23:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B22").Value) = True Then
        Rows("23:23").EntireRow.Hidden = False
        Rows("24:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B23").Value) = True Then
        Rows("24:24").EntireRow.Hidden = False
        Rows("25:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B24").Value) = True Then
        Rows("25:25").EntireRow.Hidden = False
        Rows("26:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B25").Value) = True Then
        Rows("26:26").EntireRow.Hidden = False
        Rows("27:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B26").Value) = True Then
        Rows("27:27").EntireRow.Hidden = False
        Rows("28:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B27").Value) = True Then
        Rows("28:28").EntireRow.Hidden = False
        Rows("29:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B28").Value) = True Then
        Rows("29:29").EntireRow.Hidden = False
        Rows("30:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B29").Value) = True Then
        Rows("30:30").EntireRow.Hidden = False
        Rows("31:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B30").Value) = True Then
        Rows("31:31").EntireRow.Hidden = False
        Rows("32:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B31").Value) = True Then
        Rows("32:32").EntireRow.Hidden = False
        Rows("33:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B32").Value) = True Then
        Rows("33:33").EntireRow.Hidden = False
        Rows("34:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B33").Value) = True Then
        Rows("34:34").EntireRow.Hidden = False
        Rows("35:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B34").Value) = True Then
        Rows("35:35").EntireRow.Hidden = False
        Rows("36:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B35").Value) = True Then
        Rows("36:36").EntireRow.Hidden = False
        Rows("37:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B36").Value) = True Then
        Rows("37:37").EntireRow.Hidden = False
        Rows("38:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B37").Value) = True Then
        Rows("38:38").EntireRow.Hidden = False
        Rows("39:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B38").Value) = True Then
        Rows("39:39").EntireRow.Hidden = False
        Rows("40:41").EntireRow.Hidden = True
    ElseIf IsEmpty(Range("B39").Value) = True Then
        Rows("40:40").EntireRow.Hidden = False
        Rows("41:41").EntireRow.Hidden = True
    End If
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Not really a loop but the below should do the same thing:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2:B39")) Is Nothing Then
        If IsEmpty(Target.Value) Then
            Rows(Target.Offset(1).Row).Hidden = False
            Rows(Target.Offset(2).Row & ":41").Hidden = True
        End If
    End If
End Sub
 
Upvote 0
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
This line defines a worksheet event handler. It will be triggered automatically whenever there is a change in the worksheet.
The event handler is specific to the worksheet where it is placed.
The Target parameter represents the range of cells that were changed.
VBA Code: 
If Not Intersect(Target, Range("B2:B39")) Is Nothing Then
This line checks if the changed cells intersect with the range from B2 to B39.
If the changed cells fall within this range, the subsequent code will execute.
VBA Code: 
If IsEmpty(Target.Value) Then
This line checks if the value in the changed cell (Target) is empty (i.e., contains no data).
If the cell is empty, the following actions will be performed.
VBA Code: 
Rows(Target.Offset(1).Row).Hidden = False
This line unhides the row immediately below the changed cell.
It uses the Offset(1) method to refer to the next row relative to the changed cell.
The .Hidden = False property ensures that the row becomes visible.
VBA Code: 
Rows(Target.Offset(2).Row & ":41").Hidden = True:
This line hides rows from the row two below the changed cell up to row 41.
It uses the Offset(2) method to refer to the second row below the changed cell.
The .Hidden = True property hides the specified rows.
 
Upvote 0
Georgiboy said:
Not really a loop but the below should do the same thing:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2:B39")) Is Nothing Then
        If IsEmpty(Target.Value) Then
            Rows(Target.Offset(1).Row).Hidden = False
            Rows(Target.Offset(2).Row & ":41").Hidden = True
        End If
    End If
End Sub
Click to expand...
I like that! And I really appreciate the explanations!! But for some reason it's not working. :( I replaced what I had written and saved, killed the worksheet. Restarted it and still nothing. I know I'm doing something wrong. :(
 
Upvote 0
This is not as elegant as Georgiboy but it might work for you:
VBA Code: 
For I = 2 To 39
    If IsEmpty(Range("B" & I).Value) = True Then
        Rows(I + 1 & ":" & I + 1).EntireRow.Hidden = False
        Rows(I + 2 & ":41").EntireRow.Hidden = True
        Exit sub
    End If
Next I
Put that between the private sub and end sub and see if it works.

I noticed that Your row incremented in Range B, then if true it incremented the row and hid everything below.
so it will go through the loop until it finds a true value. If you want this to happen on more than one row, then remove the exit sub for the loop.

Hope that helps.
 
Upvote 0
Solution
like this ?

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
 
    If Not Intersect(Range("B2:B41"), Target) Is Nothing Then
 
        If IsEmpty(Target.Value) = True Then
    
            Target.Offset(1, 0).EntireRow.Hidden = False
            Rows(Target.Row + 2 & ":" & "41").EntireRow.Hidden = True
        
                Else

            Rows(Target.Row + 2 & ":" & "41").EntireRow.Hidden = False
        
        End If
    End If

End Sub
 
Upvote 0
dermie_72 said:
This is not as elegant as Georgiboy but it might work for you:
VBA Code: 
For I = 2 To 39
    If IsEmpty(Range("B" & I).Value) = True Then
        Rows(I + 1 & ":" & I + 1).EntireRow.Hidden = False
        Rows(I + 2 & ":41").EntireRow.Hidden = True
        Exit sub
    End If
Next I
Put that between the private sub and end sub and see if it works.

I noticed that Your row incremented in Range B, then if true it incremented the row and hid everything below.
so it will go through the loop until it finds a true value. If you want this to happen on more than one row, then remove the exit sub for the loop.

Hope that helps.
Click to expand...
Works! Thank you! Saved a lot of room and easily altered for more lines! Thank You!
 
Upvote 0
Some things that I have noticed
  • Posts 2 & 6 do not do the same thing as the OP's code in some circumstances (though I don't know just how the OP uses the worksheet)
  • Post 5 does, as indicated by the OP's post 7 comment, though it does have some superfluous code. A row is already an entire row so there is no need to add that. Also a single row does not need to have both the first row and last row specified (since they are one and the same). So there could be a shortening of the code like this
Rich (BB code): 
Rows(I + 1 & ":" & I + 1).EntireRow.Hidden = False
Rows(I + 2 & ":41").EntireRow.Hidden = True
Rows(I + 1).Hidden = False
Rows(I + 2 & ":41").Hidden = True

I am also not a great fan of having multiple points to exit a Sub or a For .. Next Loop so here is another way that I think also does the same thing
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Long
 
  r = 1
  Do
    r = r + 1
    If IsEmpty(Range("B" & r).Value) = True Then
      Rows(r + 1).Hidden = False
      Rows(r + 2 & ":41").Hidden = True
    End If
  Loop Until IsEmpty(Range("B" & r).Value) Or r = 39
End Sub
 
Upvote 0
Peter_SSs said:
Some things that I have noticed
  • Posts 2 & 6 do not do the same thing as the OP's code in some circumstances (though I don't know just how the OP uses the worksheet)
  • Post 5 does, as indicated by the OP's post 7 comment, though it does have some superfluous code. A row is already an entire row so there is no need to add that. Also a single row does not need to have both the first row and last row specified (since they are one and the same). So there could be a shortening of the code like this
Rich (BB code): 
Rows(I + 1 & ":" & I + 1).EntireRow.Hidden = False
Rows(I + 2 & ":41").EntireRow.Hidden = True
Rows(I + 1).Hidden = False
Rows(I + 2 & ":41").Hidden = True

I am also not a great fan of having multiple points to exit a Sub or a For .. Next Loop so here is another way that I think also does the same thing
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Long
 
  r = 1
  Do
    r = r + 1
    If IsEmpty(Range("B" & r).Value) = True Then
      Rows(r + 1).Hidden = False
      Rows(r + 2 & ":41").Hidden = True
    End If
  Loop Until IsEmpty(Range("B" & r).Value) Or r = 39
End 
[/QUOTE]
This solution works as well! Thank you! I can't get over how many ways there are to achieve an answer. Like art. :) Once again, thank you!
Click to expand...
 
Upvote 0
This solution works as well! Thank you! I can't get over how many ways there are to achieve an answer. Like art. :) Once again, thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Creating a 'Reset' button for a text form, plus additional buttons
Replies
2
Views
150
WEB78
W
R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
520
John_w
J
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
403
Joe4
Joe4
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
1K
kevin9999
K
tlc53
  • Solved
Unprotect to run VBA - Why isn't it working??
Replies
1
Views
558
tlc53
tlc53

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top