#1
|
|||
|
|||
Cannot unhide rows in VBA
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 |
#2
|
|||
|
|||
Quote:
Have you tried unfiltering rather than unhiding ? |
#3
|
|||
|
|||
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 |