Using VBA to check the value of a cell from another sheet to hide a row in another sheet?

[zyntex]

Hello,

I have a file with three sheets.

Table1, Table2 and Table3.

If the cell "C2" of "Table1" has a value cell "C2" of "Table2" will get it too via the formula "=Table1!C2"
Now I want that if cell "C2" of "Table2" has no value row "6" of "Table3" gets hidden.

I had it working within the same sheet with:

Sub Test()    
If Range("C2").Value = "" Then
Rows(6).Hidden = True
Else
Rows(6).Hidden = False
End If
End Sub

At the moment I have this code however I think I messed up the link between the sheets but can't help:

Sub Test()    
If Worksheets("Table2").Cells("C2").Value = "" Then
Worksheets("Table3").Rows("6").Hidden = True
Else
Worksheets("Table3").Rows("6").Hidden = False
End If
End Sub

Also are there any resources you can recommend to get into Excel VBA and especially stuff like the above?

 

[gallen]

Hello and welcome.

I'd use this single line:

Worksheets("Table3").Rows(6).EntireRow.Hidden = Worksheets("Table2").Range("C2").Value = ""

As for getting into VBA I'd use this site plus Google is your friend. Countless tutorials, videos etc, mostly free too.

 

[footoo]

Change Rows("6") to Rows(6)

 

[zyntex]

Hello and welcome.

I'd use this single line:

Worksheets("Table3").Rows(6).EntireRow.Hidden = Worksheets("Table2").Range("C2").Value = ""

As for getting into VBA I'd use this site plus Google is your friend. Countless tutorials, videos etc, mostly free too.

Thank you. I want to use it as macro later on that's why I was writing a function. But your line doesn't work for me.

Change Rows("6") to Rows(6)

What a dump error but it doesn't help either.

I have also created a completely new document to test your suggestions without success.

 

[Fluff]

Cells("C2") should be Range("C2")

 

[footoo]

Sub Test()
If Worksheets("Table2").Range("C2").Value = "" Then
Worksheets("Table3").Rows(6).EntireRow.Hidden = True
Else
Worksheets("Table3").Rows(6).EntireRow.Hidden = False
End If
End Sub

 

[zyntex]

Hm...it doesn't work. While I get no runtime error like before when I execute the code nothing happens.

 

[footoo]

Hm...it doesn't work. While I get no runtime error like before when I execute the code nothing happens.

Do you mean that row 6 of Table3 remains unchanged regardless of the content of C2 on Table2 ?

 

[Fluff]

Try

Sub Test()
If Worksheets("Table2").Range("C2").Value = "[COLOR=#ff0000]0[/COLOR]" Then
Worksheets("Table3").Rows(6).EntireRow.Hidden = True
Else
Worksheets("Table3").Rows(6).EntireRow.Hidden = False
End If
End Sub

Your formula =Table1!C2 will return 0 if Table1 is blank, alternatively use

Sub Test()
If Worksheets("Table[COLOR=#ff0000]1[/COLOR]").Range("C2").Value = "" Then
Worksheets("Table3").Rows(6).EntireRow.Hidden = True
Else
Worksheets("Table3").Rows(6).EntireRow.Hidden = False
End If
End Sub

 

[zyntex]

Do you mean that row 6 of Table3 remains unchanged regardless of the content of C2 on Table2 ?

Exactly!

Try

Sub Test()
If Worksheets("Table2").Range("C2").Value = "[COLOR=#ff0000]0[/COLOR]" Then
Worksheets("Table3").Rows(6).EntireRow.Hidden = True
Else
Worksheets("Table3").Rows(6).EntireRow.Hidden = False
End If
End Sub

Your formula =Table1!C2 will return 0 if Table1 is blank, alternatively use

Sub Test()
If Worksheets("Table[COLOR=#ff0000]1[/COLOR]").Range("C2").Value = "" Then
Worksheets("Table3").Rows(6).EntireRow.Hidden = True
Else
Worksheets("Table3").Rows(6).EntireRow.Hidden = False
End If
End Sub

Oh my god thank you! I totally forgot that there is the 0 when it is empty because I disabled that the 0 is shown.