More AutoFilter Problems

[DSwartz]

Hi everyone! I posted last week regarding an issue with an AutoFilter macro and got some great assistance which fixed the problem. Now, however, I've attempted to expand the capabilities of my macro and I have of course encountered additional problems. Here's my code:

Sub TeamHunter()

Dim sheetname As String
Dim check As String
Dim column As String
Dim rownum As Integer
Dim totalrows As Integer
Dim totalcolumns As String
Dim teamrow As Integer
Dim teamstatus As Boolean
Dim team(1 To 30) As String
Dim teamnumber As Integer
Dim bottombound As String
Dim alldata As String
Dim columnnum As Integer

teamnumber = 1
teamrow = 13
column = Range("B5").Value
rownum = Range("B4").Value
sheetname = Range("B3").Value

Do Until teamstatus = True
If Range("A" & teamrow).Value = "" Then
teamstatus = True
Else
team(teamnumber) = Range("A" & teamrow).Value
teamrow = teamrow + 1
teamnumber = teamnumber + 1
End If
Loop

Workbooks(sheetname).Activate
ActiveSheet.AutoFilterMode = False
totalrows = ActiveSheet.UsedRange.Rows.Count
totalcolumns = ActiveSheet.UsedRange.Columns.Count

Select Case totalcolumns
    Case 1: totalcolumns = "A"
    Case 2: totalcolumns = "B"
    Case 3: totalcolumns = "C"
    Case 4: totalcolumns = "D"
    Case 5: totalcolumns = "E"
    Case 6: totalcolumns = "F"
    Case 7: totalcolumns = "G"
    Case 8: totalcolumns = "H"
    Case 9: totalcolumns = "I"
    Case 10: totalcolumns = "J"
    Case 11: totalcolumns = "K"
    Case 12: totalcolumns = "L"
    Case 13: totalcolumns = "M"
    Case 14: totalcolumns = "N"
End Select

check = column & rownum
bottombound = totalcolumns & totalrows
alldata = check & ":" & bottombound

Select Case column
    Case "A": columnnum = 1
    Case "B": columnnum = 2
    Case "C": columnnum = 3
    Case "D": columnnum = 4
    Case "E": columnnum = 5
    Case "F": columnnum = 6
    Case "G": columnnum = 7
    Case "H": columnnum = 8
    Case "I": columnnum = 9
    Case "J": columnnum = 10
    Case "K": columnnum = 11
    Case "L": columnnum = 12
    Case "M": columnnum = 13
    Case "N": columnnum = 14
End Select

ActiveSheet.Range(alldata).AutoFilter Field:=columnnum, Criteria1:=team, Operator:=xlFilterValues

End Sub

The problem I'm encountering is still on that last line of code that actually applies the filter. I'm getting the error "Run-time error 1004: AutoFilter method of Range class failed". What can I do to fix this? Thanks in advance for any assistance!

[DSwartz]

Surely I haven't stumped everyone, right? I'm sure most of you know more about VBA than I do. I'm still having this issue with my macro - any assistance would be greatly appreciated.

[jaslake]

Hi DSwatrz

Add the indicated line of Code...I believe the issue will be apparent...

check = column & rownum
bottombound = totalcolumns & totalrows
alldata = check & ":" & bottombound

Msgbox alldata  '<----Add this line of Code

[DSwartz]

Looks like that fixed it! Thanks!

[jaslake]

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save