Excel hide and show rows vba
Solved
Elstat
Posted messages
1
Status
Membre
-
eriiic Posted messages 24581 Registration date Status Contributeur Last intervention -
eriiic Posted messages 24581 Registration date Status Contributeur 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 réponses
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 (?))
eric
PS: next time use an ad-free site please, like cjoint.com
Never reply to an unsolicited DM...
Well, that's done.
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.
Je te remercie
Ton code fonctionne à merveille
J'ai juste changé cette ligne
Rows(Target.Row + IIf(Target = "", 1, 2) & ":100").EntireRow.Hidden =
POUR
Rows(Target.Row + IIf(Target = "", 2, 3) & ":100").EntireRow.Hidden =
Ton code est très court et clair
Merci encore
Eric