Cannot unhide rows in VBA

RosieSummers · #1 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

NoSparks · #2 02-12-2018, 12:39 AM

Quote:

whenever the command button is clicked, it would hide the rows with rows with "OFM" and "Collar & Cuff"

You are not hiding the rows, you are filtering them.
Have you tried unfiltering rather than unhiding ?

RosieSummers · #3 02-12-2018, 12:47 AM

Hi, thanks it worked!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
unhide hidden row	carlandtina02	Excel Programming	7	03-14-2016 02:26 PM
Hide / unhide Rows	NullVBA	Excel Programming	4	12-09-2015 05:10 AM
A row that will not UNHIDE	MaineLady	Excel	0	11-23-2014 01:15 PM
Unhide Projects in VBA Editor	Greg S.	Excel Programming	2	07-24-2013 07:31 AM
Hide/Unhide pictures	maloneb	PowerPoint	1	01-18-2012 03:34 PM