In Excel 2010 a new feature called slicers has been introduced. Slicers are widget like components that help the user filter data in a Pivot table without having to open drop-down lists. Therefore they make the data filtering quicker and easier as shown in the training video below.
What would be the result.
Private Sub UserForm_Initialize()
Dim xlrange As Excel.Range
Dim sectionName As String
Dim rowCount As Long
Dim currentRow As Long
Me.Caption = gsPROGRAMNAME
‘If gwkbNewFile Is Nothing Then Set gwkbNewFile = Workbooks(“Coca-ColaAmatilLimited_PapuaNewGuinea_S.xlsm”)
With gwkbNewFile.Worksheets(gsQRAListing)
rowCount = WorksheetFunction.CountA(.Range(“A:A”))
ReDim values(rowCount – 1, 1)
‘I’ve limited the number of rows that are checked for data to 3 times the number of
‘filled rows, this should easily cover any spaces in the row data
For Each xlrange In .Range(“A1:A” & (rowCount * 3))
If xlrange “” Then
values(currentRow, 0) = .Cells(currentRow + 1, 1)
sectionName = .Cells(xlrange.Row, xlrange.Column + 1)
If .Cells(xlrange.Row, xlrange.Column + 2) “” Then _
sectionName = sectionName & ” > ” & .Cells(xlrange.Row, xlrange.Column + 2)
sectionName = sectionName & ” > ” & .Cells(xlrange.Row, xlrange.Column + 3)
values(currentRow, 1) = sectionName
Else
Exit For
End If
currentRow = currentRow + 1
Next xlrange
End With
‘Get the Before/After Columns for the review.
With gwkbNewFile.Worksheets(gsQRATable)
Set xlrange = getLastCell(aColumn, gwkbNewFile.Worksheets(gsQRATable))
lastBefore = xlrange.Column
lastAfter = lastBefore + 1
End With
cboSection.List = values
End Sub