We help IT Professionals succeed at work.
Troubleshooting Question

Autofilter between numbers not working correctly

60 Views
Last Modified: 2020-09-29
Experts...The code in the attached workbook works in some cases and not in others. Would you take a look and let me know why not?

Autofilter and copy to Multiple Sheets.xlsm
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please let me know which case doesn't work, but I'm guessing that if you change this
If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 0 Then
                    .Columns(1).Hidden = True

Open in new window

to this
                .Columns(1).Hidden = True
                If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 0 Then

Open in new window

that it will work.
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Your numbers are formatted as Text, try changing to a Number Format

Author

Commented:
I still need to have Column one hidden, as I don't need those values...only to sort by those values.  I sort by column A, but do not copy the column A values.  I need to have the 'values' in column A set as text, to keep the leading zeros.

Author

Commented:
Martin...can you explain the resize line?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
It's just one way of determining if there are any visible rows. If the result is > 0 then there are.
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
I need to have the 'values' in column A set as text, to keep the leading zeros.

No you don't, use a Custom Numberformat - "000000000000"

Generally numbers should be used as numbers not text

Author

Commented:
the Custom Numberformat works sometimes, but not always. There were too many times when it didn't work. Especially when importing a .csv file.  Maybe if I put to text first, then custom format it might work as intended.

Author

Commented:
The filtering between two values still doesn't work with the numbers in Col A, even after changing to Custom format.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please describe what you mean when you say "doesn't work" because in your question you said it works some of the time.

Author

Commented:
Using these values in the spreadsheet. If I start with a number that is not 00, say 95, it worked fine. 

Author

Commented:
What I forgot to include in Column A values is an alpha character...hence the reason it needs to be a text value. Wait...that may be the issue. The other values where it worked on have an Alpha character in the middle.  These don't.  Need to check.

Author

Commented:
I went to my original and no they don't have an alpha character.  Why would it only work with an alpha in the middle?  Need to check.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
How about not using AutoFilter?
Sub Filter_Between_Two_Numbers()
Dim wsManifest As Worksheet
Dim wsData As Worksheet
Dim DLastRow As Long, MLastRow As Long, lRow As Long, x As Long
Dim NumOfRows(1 To 41) As Long
Dim vStart As Variant, vEnd As Variant, vBox1 As Variant, vPallet As Variant, vBox2 As Variant
Dim rngAE As Range, rng As Range
Dim StartRow As Long, EndRow As Long
Dim arrIn As Variant
Dim arrOut As Variant
Dim idxRow As Long
Dim cnt As Long

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set wsData = Worksheets("DATA")
    Set wsManifest = Worksheets("MANIFEST")

    With wsManifest
        MLastRow = .Range("A" & Rows.Count).End(xlUp).Row
        vPallet = .Range("A2:A" & MLastRow).Value
        vBox1 = .Range("B2:B" & MLastRow).Value
        vBox2 = .Range("C2:C" & MLastRow).Value
        vStart = .Range("D2:D" & MLastRow).Value
        vEnd = .Range("E2:E" & MLastRow).Value
    End With

    With wsData
        DLastRow = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngAE = .Range("A1:E" & DLastRow)

        arrIn = rngAE.Value

        For x = LBound(vStart) To UBound(vStart)
            ReDim arrOut(1 To 1, 1 To UBound(arrIn))
            cnt = 0
            For idxRow = LBound(arrIn, 1) To UBound(arrIn, 1)
                If Val(arrIn(idxRow, 1)) >= Val(vStart(x, 1)) And Val(arrIn(idxRow, 1)) <= Val(vEnd(x, 1)) Then
                    cnt = cnt + 1

                    arrOut(1, cnt) = arrIn(idxRow, 1)
                End If
            Next idxRow
            
            If cnt > 0 Then
            
                ReDim Preserve arrOut(1 To 1, 1 To cnt)
                
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = vBox2(x, 1)   'Add New Worksheet - Name Worksheet (vBox)
    
                ActiveSheet.Range("A2").Resize(cnt).Value = Application.Transpose(arrOut)
                'Add Headings
                [A1:G1].Value = Array("OBLIGOR", "DOC TYPE", "OBLIGATION", "STATUS CODE", "  ", "PALLET #", "BOX")
                [F2].Value = vPallet(x, 1)
                [G2].Value = vBox1(x, 1)
                With ActiveSheet
                    ActiveWindow.DisplayGridlines = False
                    .Columns.AutoFit
                    Set rng = .Range("A1:G" & DLastRow)
                    rng.FormatConditions.Add Type:=xlExpression, Formula1:="=mod(row()+1,2)"
                    rng.FormatConditions(1).Interior.Color = RGB(235, 235, 235)
                End With
            End If
        Next x
    End With

    With wsData
        .Columns(1).Hidden = False
        .Rows(1).Hidden = False
        .AutoFilterMode = False
        Application.Goto .Range("A1"), True
    End With

    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Open in new window

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In your Manifest data, row 4, the 'From' is greater than the 'To'. Is that correct? I believe that's why you aren't getting any results.

Author

Commented:
No, the From should be smaller.  When putting this together I was more concerned about rows 1 and 2, should have been more careful.  The code you provided, will it display all values regardless of where they are in the column?  The sorting for these is not exact, many times in doing a A-Z sort, the column values do not display a true low to high sort.

Author

Commented:
The alpha character, which is missing, will cause it to autofilter properly, or not.  I did a replace all with 0007 to 0007A, and added an alpha in row one, and it works properly, or at least it filters.  But, i have a potful of these, which are not filtering because they don't have the alpha.   I need to run Norie's code and see if it works.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I was more concerned about rows 1 and 2,
In your code you never look at those rows, but instead always the last row in the Manifest.

Author

Commented:
No... I'm copying everything into an array from the second row to the last row.

    With wsManifest
        MLastRow = .Range("A" & Rows.Count).End(xlUp).Row
        vPallet = .Range("A2:A" & MLastRow).Value
        vBox1 = .Range("B2:B" & MLastRow).Value
        vBox2 = .Range("C2:C" & MLastRow).Value
        vStart = .Range("D2:D" & MLastRow).Value
        vEnd = .Range("E2:E" & MLastRow).Value
    End With

Author

Commented:
Or into a variant...
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm copying everything into an array from the second row to the last row.

Sorry, but you're not. With the data in the workbook you posted MLastRow is 4 which is the last row,.

Author

Commented:
Norie's code seems to filter, but it doesn't copy the values in Column C to paste with those in Column B...I actually have three columns total needing to be copied and pasted..just showed two.

Author

Commented:
Doesn't vPallet, vBox1, etc. copy everything from ("A2:A" & mLastrow)  or ("B2:B" & mLastRow), etc...
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
I wasn't sure what needed to be copied as the code wasn't running.

Do you need all 3 columns on the MANIFEST sheet to be copied?

Author

Commented:
Yes, I actually have four columns in my work environment A-D.  Col A is not copied. Columns B-D are copied and pasted into their respective sheets.
Analyst Assistant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
It looks like I will need to format the cells in the new worksheets to either text or custom.  Where is the best place to put this code?
NorieAnalyst Assistant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.