Excel VBA: Creation of Array

[game_federer]

Hi All,
I have a number stored in FromMonth Variable(Say)
Eg. FromMonth = 5.

Now I need all the natural numbers less than and equal to 5 in an array and then pass into into the autofilter field.

Your help would be greatly appreciated.

Thanks,

 

[NARAYANK991]

Hi ,

Try this :

Public Sub auto_filter()
          Dim FromMonth As Integer
          Dim Crit1 As String
         
          FromMonth = 7
         
          For i = 1 To FromMonth
              Crit1 = Crit1 & "," & Str(i)
          Next
         
          Crit1 = Mid(Crit1, 2)
         
          ActiveSheet.Range("D1:L11").AutoFilter Field:=4, Criteria1:=Split(Crit1, ","), Operator:=xlFilterValues
End Sub

Change the highlighted portions to suit.

Narayan

 

[game_federer]

Hi Narayan,

It worked perfectly.
Thank you for your time and knowledge.

 

[Marc L]

Hi,

just using standard Excel :​

    FromMonth% = 5
    V = Evaluate("TRANSPOSE(ROW(1:" & FromMonth & "))")