Microsoft Office Forums - View Single Post

RosieSummers · 02-11-2018, 11:28 PM

Hi there,

i have a command button that retrieves it data from an external workbook according to date.it this case it is March 2018.
The command button will delete all the records and paste it all again whenever the command button is clicked. however, it would not delete

the problem i faced is whenever the command button is clicked, it would hide the rows with rows with "OFM" and "Collar & Cuff". i have tried to use a vba code to unhide it and to change the row height but it can only show the first row hidden
does anyone know why?

code for my command button to retrive the records from the external data:

Code:

Sub zz()
Dim arr, c, b(), n&
Application.ScreenUpdating = False
Worksheets("Sheet2").Range("A6:W" & Rows.Count).AutoFilter
Workbooks.Open "C:\Users\sophia.tan\Desktop\MasterPlanData.xlsx", 0, 1
arr = Sheets("Excel").UsedRange
ActiveWorkbook.Close 0
c = Array(0, 2, 13, 14, 7, 8, 11, 1, 9, 10, 16, 17, 20, 22, 15, 30, 27, 28, 29, 3, 4, 39)
d = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 19, 20, 21, 23)
ReDim b(1 To UBound(arr), 1 To 23)

For i = 2 To UBound(arr)
    If arr(i, 13) >= DateSerial(Year:=2018, Month:=3, Day:=1) And arr(i, 12) <= DateSerial(Year:=2018, Month:=3, Day:=31) Then
        n = n + 1
        For j = 1 To UBound(c)
            b(n, d(j)) = arr(i, c(j))
        Next
    End If
Next


With Worksheets("Sheet2")
    .Range("A6:W" & Rows.Count).CurrentRegion.SpecialCells(xlCellTypeVisible).AutoFilter field:=1, Criteria1:="<>OFM"
    .Range("A6:W" & Rows.Count).CurrentRegion.SpecialCells(xlCellTypeVisible).AutoFilter field:=13, Criteria1:="<>Collar & Cuff"
    .Range("A6:W" & Rows.Count).CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .Range("A6:W" & Rows.Count).Resize(UBound(b, 1), UBound(b, 2)) = b
  
    
    .Range("A6").CurrentRegion.Sort key1:=Range("G6"), order1:=xlAscending, Header:=xlYes
    .Range("A6").Select


End With
Dim startRow As Long, lastRow2 As Long
    startRow = 6
    lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

    For i = startRow To lastRow
   
    If Range("A" & i) Like "MX*" Then
        
        If Range("J" & i) Like "*Rib*" Then
             Range("M" & i) = "Rib"
              
        ElseIf Range("J" & i) Like "*Spandex*Pique*" Then
             Range("M" & i) = "Spandex Pique"
                 
        ElseIf ("J" & i) Like "*Pique*" Then
             Range("M" & i) = "Pique"
               
        ElseIf ("J" & i) Like "*Spandex*Jersey*" Then
             Range("M" & i) = "Spandex Jersey"
        
        ElseIf Range("J" & i) Like "*Jersey*" Then
             Range("M" & i) = "Jersey"
        
        ElseIf ("J" & i) Like "*Interlock*" Then
             Range("M" & i) = "Interlock"
             
        ElseIf ("J" & i) Like "*French*Terry*" Then
             Range("M" & i) = "Fleece"
    
        ElseIf ("J" & i) Like "*Fleece*" Then
             Range("M" & i) = "Fleece"
    
        Else
        

         Range("M" & i) = "Collar & Cuff"
        
        
        End If
        End If
        
 Next
 


Application.ScreenUpdating = 1

End Sub