Excel hide and show rows vba
Solved
Elstat
Posted messages
1
Status
Member
-
eriiic Posted messages 24581 Registration date Status Contributor Last intervention -
eriiic Posted messages 24581 Registration date Status Contributor Last intervention -
Hello everyone
I have some lines on a page that I want to hide if column G from row 34 onward is empty, and I want to hide the rows from 36 to 100.
For example: if I place a value in cell G35 I want rows G37 to G100 to be hidden, and if I remove the value from cell G35 I want rows G36 to G100 to be hidden.
I am looking to simplify a list of orders
If [G34] = "" Then Rows("36:100").EntireRow.Hidden = True
If [G35] = "" Then Rows("37:100").EntireRow.Hidden = True
If [G36] = "" Then Rows("38:100").EntireRow.Hidden = True
.
.
.
If [G98] = "" Then Rows("100:100").EntireRow.Hidden = True
Here is my code attempt and thank you for your help
Sub Hide()
Dim I As Long
I = 34
Do While I < 101
If Cells(I, 7) = "" Then Cells(I + 2, 100).EntireRow.Hidden = True
End If
I = I + 1
Loop
End Sub
I have some lines on a page that I want to hide if column G from row 34 onward is empty, and I want to hide the rows from 36 to 100.
For example: if I place a value in cell G35 I want rows G37 to G100 to be hidden, and if I remove the value from cell G35 I want rows G36 to G100 to be hidden.
I am looking to simplify a list of orders
If [G34] = "" Then Rows("36:100").EntireRow.Hidden = True
If [G35] = "" Then Rows("37:100").EntireRow.Hidden = True
If [G36] = "" Then Rows("38:100").EntireRow.Hidden = True
.
.
.
If [G98] = "" Then Rows("100:100").EntireRow.Hidden = True
Here is my code attempt and thank you for your help
Sub Hide()
Dim I As Long
I = 34
Do While I < 101
If Cells(I, 7) = "" Then Cells(I + 2, 100).EntireRow.Hidden = True
End If
I = I + 1
Loop
End Sub
2 answers
-
Good evening,
Try this.
I based it on the text:
for example: if I enter a value in cell G35 I want rows G37 to G100 to be hidden, and if I remove the value from cell G35 I want rows G36 to G100 to be hidden.
without considering whether the cells below are full or empty (which you do in your code (?))
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, [G36:G100]) Is Nothing Then Exit Sub Rows("36:100").EntireRow.Hidden = False Rows(Target.Row + IIf(Target = "", 1, 2) & ":100").EntireRow.Hidden = True End Sub
eric
PS: next time use an ad-free site please, like cjoint.com
Never reply to an unsolicited DM...
Well, that's done.
