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

Z

zyntex

New Member
Joined
Aug 16, 2017
Messages
6
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:
Code: 
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:
Code: 
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?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hello and welcome.

I'd use this single line:
Code: 
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.
 
Upvote 0
gallen said:
Hello and welcome.

I'd use this single line:
Code: 
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.
Click to expand...

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.


footoo said:
Change Rows("6") to Rows(6)
Click to expand...

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

I have also created a completely new document to test your suggestions without success.
 
Upvote 0
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
 
Upvote 0
Hm...it doesn't work. While I get no runtime error like before when I execute the code nothing happens.
 
Upvote 0
zyntex said:
Hm...it doesn't work. While I get no runtime error like before when I execute the code nothing happens.
Click to expand...

Do you mean that row 6 of Table3 remains unchanged regardless of the content of C2 on Table2 ?
 
Upvote 0
Try
Code: 
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
Code: 
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
 
Upvote 0
footoo said:
Do you mean that row 6 of Table3 remains unchanged regardless of the content of C2 on Table2 ?
Click to expand...

Exactly!


Fluff said:
Try
Code: 
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
Code: 
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
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
357
GardianAngelUK
G
N
  • Question
VSTACK data from multiple sheets and filter results for specific dates
Replies
2
Views
205
newxl
N
B
  • Question
Excel VBA When new rows are added to table 1 copy the information to table 2
Replies
0
Views
466
BlueHawker42
B
T
  • Question
Row Number of the Source Sheet's Row, from Which the Current/Different Sheet's Cell Derived its Contents
Replies
6
Views
174
Fluff
Fluff
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
560
kevin9999
K

Forum statistics

Threads
1,215,842
Messages
6,127,225
Members
449,371
Latest member
strawberrish

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