Issue with Creating macro to hide series of rows based off of Dropdown list

[Rcoates]

Hello,

I have a dropdown list which contains a list of dates. When I select a date from the list (e.g. 1/31/2011) I wish for only a selected amount of rows in the worksheet to be shown, and then everything else will be hidden.

Below I have what I have created so far in the macro, but it doesn't seem to work correctly. I eventually would like to have over 50 dates to choose from (with each date only showing the 150 rows that pertain to it).

Could someone take a look at this and let me know what needs to be changed? Thanks



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
With Range("A3")
Range("150:20000").EntireRow.Hidden = .Value = "1/31/2011"
Range("6:149,300:20000").EntireRow.Hidden = .Value = "2/28/2011"
Range("6:299,450:20000").EntireRow.Hidden = .Value = "3/31/2011"
End With
End If
End Sub

 

[VoG]

Perhaps like this

Rows("150:20000").Hidden = .Value = DateValue("1/31/2011")

 

[Rcoates]

Thanks for replying.

Even with using "DateValue" I still find that this only works for 3/31/2011. The other two dates nothing happens, any suggestions?

Thanks

 

[VoG]

You'd need to split each of those onto two lines like this

Rows("6:149").Hidden = .Value = DateValue("2/28/2011")
Rows("300:20000").Hidden = .Value = DateValue("2/28/2011")

 

[GTO]

When you click in the cell and check the number format, is it coming back a legitimate date, or text?

 

[Rcoates]

Thanks for all the help. The format is in date text. I changed the coding to what is shown below and I am still having the same issue.

I played around with the arrangement of the arguments and it seems as though only the last value is working (I.E. only 3/31 since it is the last item before "end if"). Hopefully that helps shed some light on the issue. Any ideas?


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
With Range("A3")
Range("150:2000").EntireRow.Hidden = .Value = DateValue("1/31/2011")
Range("6:149,300:2000").EntireRow.Hidden = .Value = DateValue("2/28/2011")
Range("300:2000").EntireRow.Hidden = .Value = DateValue("2/28/2011")
Range("6:299").EntireRow.Hidden = .Value = DateValue("3/31/2011")
Range("450:2000").EntireRow.Hidden = .Value = DateValue("3/31/2011")
End With
End If
End Sub

 

[Rcoates]

Hey - Any idea why this would only work for the last argument?