Results 1 to 6 of 6

Visibledropdown false not hiding all arrows

Threaded View

  1. #1
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Visibledropdown false not hiding all arrows

    I am probaly being a bit slow today (Feel it) but if anyone could look at the code below. I have put some code right at the end to hide the filter dropdown arrows, which is working to a point.
    That "point" is column I but it is not removing the arrows after this point (See results sheet). If anyone can point out what stupid action I have (Or haven't) done to cause it to stop there I would be grateful.

    Sub ALCOHOLPICK()
    
    Application.ScreenUpdating = False
        ChDir "C:\Documents and Settings\PAULW\Desktop"
        Workbooks.OpenText Filename:= _
            "C:\Documents and Settings\PAULW\Desktop\ALCOHOL MASTER.txt", Origin:= _
            xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
            1), Array(5, 1), Array(13, 1), Array(18, 1), Array(43, 1), Array(49, 1), Array(53, 1), Array _
            (59, 1), Array(66, 1), Array(72, 1), Array(78, 1), Array(92, 1), Array(100, 1), Array(105, 1 _
            ), Array(113, 1), Array(120, 1), Array(125, 1))
         Rows("1:1").Select
        Selection.Insert Shift:=xlDown
            
            Columns("C:C").Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.EntireRow.Delete
        
           Dim ws1 As Worksheet
        Dim ws2 As Worksheet
    
        Set ws1 = Worksheets("ALCOHOL MASTER")
        Dim rstart As Long
        Dim rEnd As Long
        Dim r As Long
        Dim i As Long
        Dim LResult1 As String
        Dim Lresult2 As String
        Dim Txt1 As String
        Dim Txt2 As String
        Dim ColA As String
        Dim ColC As String
        Dim ColH As Long
                        
        rstart = 1
        rEnd = ws1.Cells(Rows.Count, 1).End(xlUp).Row
        r = rEnd
        ws1.Select
        LResult1 = ""
        Lresult2 = ""
        Txt1 = ""
        Txt2 = ""
        ColA = ""
        ColC = ""
        ColH = 0
        For i = 1 To r
            ColA = Range("A" & rstart + i).Value
            If Left(ColA, 1) = "-" Then
                Range("B" & rstart + i).Select
                'ActiveCell.FormulaR1C1 = "Found Dotted Line"
                Rows(rstart + i & ":" & rstart + i).Select
                Selection.Delete Shift:=xlUp
                
     End If
            Next i
       
       Dim vList, lArrCounter As Long
    Dim rFOUND As Range, delRNG As Range, rFIRST As String
        
    
    
    
    vList = Array("Deli", "O R D E R E D", "G Description", "Produc", "Suppl", ".ORDPRODS v10.81 ORDERED", "ANALY", "REPTS", "Book", "Fall")
        
        For lArrCounter = LBound(vList) To UBound(vList)
           With Worksheets("ALCOHOL MASTER").UsedRange
                Set rFOUND = .Find( _
                    What:=vList(lArrCounter), _
                    LookIn:=xlValues, _
                    Lookat:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=True)
                
                If Not rFOUND Is Nothing Then
                    rFIRST = rFOUND.Address
                    Do
                        If delRNG Is Nothing Then
                            Set delRNG = Range("A" & rFOUND.Row)
                        Else
                            Set delRNG = Union(delRNG, Range("A" & rFOUND.Row))
                        End If
                        
                        Set rFOUND = .FindNext(After:=rFOUND)
                        
                    Loop Until rFOUND.Address = rFIRST
                End If
                Set rFOUND = Nothing
            End With
        Next lArrCounter
        
    If Not delRNG Is Nothing Then delRNG.EntireRow.Delete
    
    
     
        
        Range("R2").Select
        ActiveCell.FormulaR1C1 = "=IF(MOD(ROW(),1)=1, """", R[-1]C[-15])"
        Range("R2").Select
        Selection.Copy
        Range("R2:R500").Select
        ActiveSheet.Paste
        Columns("R:R").Select
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Rows("1:20").Select
    Selection.Columns.AutoFit
        Selection.Sort Key1:=Range("O2"), Order1:=xlAscending, Key2:=Range("Q2") _
            , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
            , Orientation:=xlTopToBottom
        
        Rows("1:1").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=15, Criteria1:="Trans"
        Rows("140:388").Select
        Range("C93").Activate
        Selection.Delete Shift:=xlUp
        Columns("K:L").Select
        Selection.EntireColumn.Hidden = True
        Columns("F:F").Select
        Selection.EntireColumn.Hidden = True
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        
        Application.ScreenUpdating = False
        
        ActiveSheet.PageSetup.PrintArea = ""
        
    Cells.Select
        Selection.AutoFilter
        Selection.AutoFilter
        Selection.AutoFilter Field:=15, Criteria1:="Trans"
        
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
       
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "Supp"
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "Bin"
        Range("C1").Select
        ActiveCell.FormulaR1C1 = "SngC"
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "Product"
        Range("E1").Select
        ActiveCell.FormulaR1C1 = "Pack"
        Range("G1").Select
        ActiveCell.FormulaR1C1 = "Ord"
        Range("H1").Select
        ActiveCell.FormulaR1C1 = "Bal"
        Range("J1").Select
        ActiveCell.FormulaR1C1 = ""
        Range("M1").Select
        ActiveCell.FormulaR1C1 = "Conv"
        Range("N1").Select
        ActiveCell.FormulaR1C1 = "+/-"
        Range("O1").Select
        ActiveCell.FormulaR1C1 = "Act"
        Range("P1").Select
        ActiveCell.FormulaR1C1 = "+/-"
        Range("Q1").Select
        ActiveCell.FormulaR1C1 = "CBin"
        Range("R1").Select
        ActiveCell.FormulaR1C1 = "C.Code"
    
        End With
        ActiveSheet.PageSetup.PrintArea = ""
        With ActiveSheet.PageSetup
            .Orientation = xlLandscape
            .Zoom = False
        Columns("A:R").Select
        Selection.Columns.AutoFit
        Columns("K:L").Select
        Selection.EntireColumn.Hidden = True
        .FitToPagesWide = 1
            .FitToPagesTall = 2
    End With
    Dim c As Range
    i = Cells(1, 1).End(xlToRight).Column
    For Each c In Range(Cells(1, 1), Cells(1, i))
    c.AutoFilter Field:=c.Column, visibledropdown:=False
    Next
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by WasWodge; 12-21-2010 at 07:16 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1