• 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

  • Example Worksheet.xlsm
    11.9 KB · Views: 3
Elijah Red
Is there any clear differences with this 'few minutes earlier thread'?
 
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.xlsm
    16.7 KB · Views: 4
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?
 
So if we were doing this for three cells that needed to disappear for vegetable, what would that formula look like?
 
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.
 
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
 
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
 
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
 
Back
Top