Want drop down list not to show hidden data

[saints49]

Hi Expert friends, I hope to get some help.

I have on Sheet 1, a drop down list. In its Data Validation Source, I have the following:
=offset(Sheet2!$A$2,0,0,CountA(Sheet2!$A:$A)-1,1).
It lists all the contents in Column A in Sheet2.

However, sometimes I may hide some of these contents.
How do I change the source formula, so that the drop down list will show the non-hidden contents only?
The above formula still list all the contents even though I have some of them hidden.
Any help is appreciated. Thank you.

 

[Debraj]

Hi Saints..

I guess.. its in continuation of your previous post..

Try this.. to handle both case.. previous query & New One too...

Sub delete_B2()
  If Len(Range("B2")) = 0 Then
  MsgBox "  There is nothing to delete!", , "ALERT !"
  Else
  If MsgBox("Are you sure that you wish to delete the content?", vbYesNo, "CONFIRM") = vbYes Then
  Sheet2.Range("A" & Application.Match([b2], Sheet2.Range("A:A"), False)).EntireRow.Delete xlShiftUp
  editDD
  MsgBox "The content has been deleted !"
  End If
  End If
End Sub
Private Sub Worksheet_Activate()
  editDD
End Sub

Sub editDD()
With Sheet2
  For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
  If .Cells(i, "A").Rows.Hidden = False Then _
  raj = raj & "," & .Cells(i, "A")
  Next i
End With
  Sheet1.Range("B2").Validation.Modify 3, 1, 1, Mid(raj, 2)
  Sheet1.Range("B2").ClearContents
End Sub