• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Disappearing and Moving Cells

Elijah Red

New Member
My colleague and I were given a task to have a cell stay or disappear and changed based on a drop down. So for example, in the attached spread sheet we would need the Seedless category to disappear when vegetable is selected. Then the Color would move up in its place. Is there a way to do this with VBA? Also, this example was made quickly before work ended, so don't laugh to hard.
 

Attachments

vletm

Excel Ninja
Elijah Red
Is there any clear differences with this 'few minutes earlier thread'?
 

Fluff13

Active Member
Is this what you mean
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B1" Then
      Rows(4).Hidden = Target.Value = "Vegetable"
   End If
End Sub
 

Attachments

Elijah Red

New Member
Is this what you mean
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B1" Then
      Rows(4).Hidden = Target.Value = "Vegetable"
   End If
End Sub
Yeah that is exactly what we were looking for. Thank you a lot! Is it possible for this to go to a much wider scale?
 

Elijah Red

New Member
So if we were doing this for three cells that needed to disappear for vegetable, what would that formula look like?
 

Elijah Red

New Member
Sadly we can't give you the spreadsheet we are working on and need this for lol. Could you make all the rows disappear with that macro when vegetable is selected? That would give us the macro that we are looking for.
 

Fluff13

Active Member
Could you make all the rows disappear with that macro when vegetable is selected?
Yup, try
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B1" Then
      Rows("1:" & Rows.Count).Hidden = Target.Value = "Vegetable"
   End If
End Sub
 

Elijah Red

New Member
Can you make the type, seedless, and color rows disappear? You hid all the cells with that one, which is what I asked for by accident o_O lol
 

Fluff13

Active Member
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B1" Then
      Rows("3:5").Hidden = Target.Value = "Vegetable"
   End If
End Sub
 
Top