Hide MSHFlexgrid2 rows if

Actually, it would be better to delete all row that are different from my list box list checked.

Is this easier?

do you need help filling the listbox?

Well, the list box is ok.

I just need help that if i select values in the list box, that it will deleted rows in MSHFlexgrid that does not match with value in my list box.

Please look at this picture.

If i select multiple value in my list on the right, it will delete rows in the grid that does not match with my list.

I can have 1 or more then 1 value check in my list.
delete-grid-row.JPG

How would you feed the list box.

What i did waht to add them manually.

Are you suggesting to feed the list box automatically with all value from column 1 has single value?

The less typing required by your user, the fewer typos and the better their UI experience.  Moreover, it is a waste of their time entering data that already exists.

How are you populating your grid?

Are you using a standard listbox?

Do you allow the user to select several items before applying the filter?

=========
Before continuing, it might be helpful to understand how the filtered grid contents will be used.

Hello again,

Q. How are you populating your grid?

This is the code i use, without any filter.

Private Sub Command2_Click()

'HEADING FOR GRID
 MSHFlexGrid2.Clear
MSHFlexGrid2.Rows = 2
MSHFlexGrid2.Cols = 2

Label12.Caption = ""

Dim xlObject     As Excel.Application
Dim xlWb         As Excel.Workbook
Dim NoOfRows     As Long
Dim NoOfColumns  As Long

    On Error GoTo MyErrHandler
   
    With CommonDialog1
        .CancelError = True
        .Filter = "Microsoft Excel files (xlam, xlsx, xltm, xlt, xlsm, xltx, xls, txt, csv)"
        .InitDir = "C:\Documents and Settings\all users\Desktop"
        .ShowOpen
        If Not .FileName = "" Then
            Set xlObject = New Excel.Application
            Set xlWb = xlObject.Workbooks.Open(.FileName)

            Clipboard.Clear
            xlObject.Cells.Copy     ' Copy all cells in active worksheet.
            FetchNoRowCol xlObject.ActiveWorkbook.ActiveSheet, NoOfRows, NoOfColumns
            With MSHFlexGrid2
               .Redraw = False     'Dont draw until the end, so we avoid that flash
               .Rows = NoOfRows
               .Cols = NoOfColumns
               .Row = 0            'Paste from first cell
               .Col = 0
               .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
               .ColSel = .Cols - 1
               .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
               .Col = 1            'Just to remove that blue selection from Flexgrid
               .Redraw = True      'Now draw
            End With
            xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
            xlWb.Close
            xlObject.Application.Quit
            Set xlWb = Nothing
            Set xlObject = Nothing
        End If
    End With
   

'Concatenate
Dim k As Long

For k = 1 To MSHFlexGrid2.Rows - 1

If MSHFlexGrid2.TextMatrix(k, 1) <> "" Then
   MSHFlexGrid2.TextMatrix(k, 15) = MSHFlexGrid2.TextMatrix(k, 0) & MSHFlexGrid2.TextMatrix(k, 4) & MSHFlexGrid2.TextMatrix(k, 1) & MSHFlexGrid2.TextMatrix(k, 6) & MSHFlexGrid2.TextMatrix(k, 2) & MSHFlexGrid2.TextMatrix(k, 7) & MSHFlexGrid2.TextMatrix(k, 8) & MSHFlexGrid2.TextMatrix(k, 9) & MSHFlexGrid2.TextMatrix(k, 10)
   
  End If

Next k


 'Auto column fit
    Dim r As Long
Dim C As Long
Dim cell_wid As Single
Dim col_wid As Single

    For C = 0 To MSHFlexGrid2.Cols - 1
        col_wid = 0
        For r = 0 To MSHFlexGrid2.Rows - 1
            cell_wid = TextWidth(MSHFlexGrid2.TextMatrix(r, C))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next r
        MSHFlexGrid2.ColWidth(C) = col_wid + 120
    Next C
    
    
'Count
        Dim z As Long, txt As String, total As Long
For z = 1 To MSHFlexGrid2.Rows - 1
  If Len(MSHFlexGrid2.TextMatrix(z, 3)) Then total = total + 1

Next z
lblTotalrecord1 = CStr(total)


Command3.Enabled = True
Command4.Enabled = True



If lblTotalrecord <> "" And lblTotalrecord1 <> "" Then
Command5.Enabled = True
Command7.Enabled = True

Else
Command5.Enabled = False
Command7.Enabled = False
End If


   With MSHFlexGrid2
         '.Col = 0
       .ColSel = 1
       .Sort = flexSortStringAscending
    End With
    
    

    Exit Sub

MyErrHandler:
    Err.Clear
    

End Sub

Select allOpen in new window

Q. Are you using a standard listbox?

I use a Listbox style: 1-Checkbox.


Q.Do you allow the user to select several items before applying the filter?

Yes, the user could select 1 or more value from the list box, depending on the value he want's to see in the grid.

How can i do this? is it possible?

Yes :)

My suggestion is to use two listboxs. Set the visible property to one of them to false. Both listboxes will mirror each other. Now when you select an item from LB1 then rest of the values from Flex will be deleted except the selected item. You can use LB2 later to repopulate the flex. :)

Let me know if you need a sample :)

Sid

Ok,

I have just put another list box.

List1 and list2.

List1 is now set to false.

I would really appreciate the example. I really don't know how to do this.

Thanks again

Sure. Is it the same project with which I helped earlier?

Sid

Just want to make sure that it has to filter directly in the grid and not to export in excel.

Thanks again

I don't see a need to have two listbox controls in this case.  I'll post some code in a few minutes.

Ok I will wait for your code before starting on this one aiki :)

Sid

Ok Here is a simple example without using List2 :)

Simple run the exe and extract the folder.

HTH

Sid

Code Used

Private Sub Form_Load()
    Dim i As Long, j As Long, Rw As Long, Cl As Long
    MSHFlexGrid1.Cols = 1
    MSHFlexGrid1.Rows = 100
    
    For i = 1 To 99
        MSHFlexGrid1.TextMatrix(i, 0) = i + 1
        List1.AddItem i
    Next i

    MSHFlexGrid1.TextMatrix(0, 0) = "Sample"
End Sub

Private Sub List1_Click()
    MSHFlexGrid1.Clear
    MSHFlexGrid1.TextMatrix(0, 0) = "Sample"
    j = 1
    For i = 0 To List1.ListCount - 1
        If List1.Selected(i) Then
            MSHFlexGrid1.TextMatrix(j, 0) = List1.List(i)
            j = j + 1
        End If
    Next
End Sub

Select allOpen in new window

HideRows.exe

Thanks again for your time and help.

Seems like we posted at the same time :)

Check the sample that I uploaded above.

Sid

Consider the following code:

Option Explicit

Sub FetchNoRowCol(parmSheet As Excel.Worksheet, parmRows As Long, parmCols As Long)
    parmRows = parmSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    parmCols = parmSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
End Sub

Private Sub Command1_Click()

    'HEADING FOR GRID
    MSHFlexGrid2.Clear
    MSHFlexGrid2.Rows = 2
    MSHFlexGrid2.Cols = 2
    
    Label12.Caption = ""
    
    Dim xlObject     As Excel.Application
    Dim xlWb         As Excel.Workbook
    Dim NoOfRows     As Long
    Dim NoOfColumns  As Long
    
    Dim vArray As Variant
    Dim lngRow As Long
    Dim lngCol As Long
    Dim dicUnique As New Scripting.Dictionary
    Dim vItem As Variant
    
    On Error GoTo MyErrHandler
   
    With CommonDialog1
        .CancelError = True
        .Filter = "Microsoft Excel files (xlam, xlsx, xltm, xlt, xlsm, xltx, xls, txt, csv)"
        .filename = "*.xl*"
        .InitDir = "C:\Documents and Settings\all users\Desktop"
        .ShowOpen
        If Not .filename = "" Then
            Set xlObject = New Excel.Application
            Set xlWb = xlObject.Workbooks.Open(.filename)

            'Clipboard.Clear
            'xlObject.Cells.Copy     ' Copy all cells in active worksheet.
            FetchNoRowCol xlObject.ActiveWorkbook.ActiveSheet, NoOfRows, NoOfColumns
            With xlObject.ActiveWorkbook.ActiveSheet
                vArray = .Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell))
            End With
            With MSHFlexGrid2
               .Redraw = False     'Dont draw until the end, so we avoid that flash
               .Rows = NoOfRows
               .Cols = NoOfColumns
               .Row = 0            'Paste from first cell
               .Col = 0
               .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
               .ColSel = .Cols - 1
               '.Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
               .Col = 1            'Just to remove that blue selection from Flexgrid
               .Redraw = True      'Now draw
               For lngRow = 1 To NoOfRows
                 For lngCol = 1 To NoOfColumns
                    .TextMatrix(lngRow - 1, lngCol - 1) = vArray(lngRow, lngCol)
                 Next
               Next
            End With
            xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
            xlWb.Close
            xlObject.Application.Quit
            Set xlWb = Nothing
            Set xlObject = Nothing
        End If
    End With

   'Fill listbox with unique values from first column in flexgrid
    Me.List1.Clear
    For lngRow = 1 To NoOfRows
        vItem = MSHFlexGrid2.TextMatrix(lngRow, 0)
        If dicUnique.Exists(vItem) Then
        Else
            dicUnique.Add vItem, 1
            List1.AddItem vItem
        End If
    Next
    dicUnique.RemoveAll
    Set dicUnique = Nothing
    
    'Concatenate
    Dim k As Long
    
    For k = 1 To MSHFlexGrid2.Rows - 1
    
        If MSHFlexGrid2.TextMatrix(k, 1) <> "" Then
           MSHFlexGrid2.TextMatrix(k, 15) = MSHFlexGrid2.TextMatrix(k, 0) & MSHFlexGrid2.TextMatrix(k, 4) & MSHFlexGrid2.TextMatrix(k, 1) & MSHFlexGrid2.TextMatrix(k, 6) & MSHFlexGrid2.TextMatrix(k, 2) & MSHFlexGrid2.TextMatrix(k, 7) & MSHFlexGrid2.TextMatrix(k, 8) & MSHFlexGrid2.TextMatrix(k, 9) & MSHFlexGrid2.TextMatrix(k, 10)
        End If
    
    Next k
    
    
     'Auto column fit
    Dim r As Long
    Dim C As Long
    Dim cell_wid As Single
    Dim col_wid As Single

    For C = 0 To MSHFlexGrid2.Cols - 1
        col_wid = 0
        For r = 0 To MSHFlexGrid2.Rows - 1
            cell_wid = TextWidth(MSHFlexGrid2.TextMatrix(r, C))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next r
        MSHFlexGrid2.ColWidth(C) = col_wid + 120
    Next C
    
    
'Count
    Dim z As Long, txt As String, total As Long
    For z = 1 To MSHFlexGrid2.Rows - 1
      If Len(MSHFlexGrid2.TextMatrix(z, 3)) Then total = total + 1
    Next z
    
    lblTotalrecord1 = CStr(total)


    Command3.Enabled = True
    Command4.Enabled = True

    If lblTotalrecord <> "" And lblTotalrecord1 <> "" Then
        Command5.Enabled = True
        Command7.Enabled = True

    Else
        Command5.Enabled = False
        Command7.Enabled = False
    End If


    With MSHFlexGrid2
       .ColSel = 1
       .Sort = flexSortStringAscending
    End With

    Exit Sub

MyErrHandler:
    Err.Clear
    
End Sub


Private Sub Command2_Click()
    Dim lngRow As Long
    Dim lngListLoop As Long
    Dim dicUnique As New Scripting.Dictionary
    Dim lngRowHeight As Long
    lngRowHeight = MSHFlexGrid2.RowHeight(0)
    For lngListLoop = 0 To List1.ListCount - 1
        If List1.Selected(lngListLoop) Then
            dicUnique.Add List1.List(lngListLoop), 1
        End If
    Next
    
    For lngRow = 1 To MSHFlexGrid2.Rows - 1     'skip header row
        If dicUnique.Exists(MSHFlexGrid2.TextMatrix(lngRow, 0)) Then
            MSHFlexGrid2.RowHeight(lngRow) = lngRowHeight
        Else
            MSHFlexGrid2.RowHeight(lngRow) = 0  'hide
        End If
    Next
    dicUnique.RemoveAll
    Set dicUnique = Nothing
End Sub

Select allOpen in new window

Notes:
* FetchNoRowCol() is my version of missing routine
* I have commented the setting of the .Clip property
* I avoid using the clipboard, so I'm using a variant array to transfer the data from the spreadsheet to the flexgrid.  For more information on this, but in the opposite direction, please read my Fast Data Push to Excel article
https://www.experts-exchange.com/A_2253.html
* My Fill Grid command button name is Command1.
* My filter grid command button name is Command2.
* You should explicitly name your objects instead of letting them default.  I would normally name such command buttons as cmdFillGrid and cmdFilterGrid
* Although I'm filling the listbox with unique values from the flexgrid, you will get better performance if you use the vArray values
* I added a few new variables
    Dim vArray As Variant
    Dim lngRow As Long
    Dim lngCol As Long
    Dim dicUnique As New Scripting.Dictionary
    Dim vItem As Variant
* You will need to add a reference to the Microsoft Scripting Runtime in order for this code to compile cleanly.  Alternatively, you can do a late binding of the dicUnique object using CreateObject() - in lieu of the reference.
* I formatted your code, trying to make it a bit more readable.

perfect. I will test this right now and let you know about the result.

Thanks again :)

@Sid

I thought you were going to wait until I posted.

Yeah That was the plan originally and I did wait.:)

>>>>I'll post some code in a few minutes.
You said you will take few mins but when you didn't post for an hour so I thought you were busy and hence I posted. :)

Sid

After some test, it works but it is very slow.

Since the grid have more then7000 rows, it is very slow.

I wonder is it would be better to just deleted the rows instead of hiding the rows?

ah...difference in "few minutes" interpretation.  I should have used "shortly".

It isn't as quick and easy to change and test code that references controls and based on (assumptions about) the imported worksheet data.  My delay is probably best reflected in the notes accompanying the code.

@Wilder1626

>>I wonder is it would be better to just deleted the rows instead of hiding the rows?

If you just hide the rows, your user will be able to do incremental filtering without waiting for the grid to be repopulated.

For a consistent UI, you would probably iterate through the listbox items, setting their .Selected property = True.  (when you populate the grid initially)

You would have a friendlier UI if you allowed the user to select all or deselect all listbox items with a command button or keyboard action.

oh yes, this is way faster.

Let me try something else, just to finish the testing.

Also, move the .Redraw=True statement below my nested loops in the command1_click event.

In this UI improvement, I change the mouse pointer to hourglass during the filtering process and then give the user a count of the number of filtered rows.  You could also add some kind of progress indicator to your form, but that seems like overkill.  It only takes a couple of seconds on this old 800MHz system of mine.

Note: a msgbox isn't the best UI implementation, but I'm showing this as an example of feedback to the user about the results of their filtering.

Private Sub Command2_Click()
    Dim lngRow As Long
    Dim lngListLoop As Long
    Dim dicUnique As New Scripting.Dictionary
    Dim lngRowHeight As Long
    Dim lngCount As Long
    
    lngRowHeight = MSHFlexGrid2.RowHeight(0)
    For lngListLoop = 0 To List1.ListCount - 1
        If List1.Selected(lngListLoop) Then
            dicUnique.Add List1.List(lngListLoop), 1
        End If
    Next
    Me.MousePointer = vbHourglass
    MSHFlexGrid2.Redraw = False
    For lngRow = 1 To MSHFlexGrid2.Rows - 1     'skip header row
        If dicUnique.Exists(MSHFlexGrid2.TextMatrix(lngRow, 0)) Then
            MSHFlexGrid2.RowHeight(lngRow) = lngRowHeight
            lngCount = lngCount + 1
        Else
            MSHFlexGrid2.RowHeight(lngRow) = 0  'hide
        End If
    Next
    MSHFlexGrid2.Redraw = True
    Me.MousePointer = vbDefault
    dicUnique.RemoveAll
    Set dicUnique = Nothing
    MsgBox "Filtered Rows: " & lngCount
End Sub

Select allOpen in new window

you are saying like this?

   With MSHFlexGrid2
               .Redraw = False     'Dont draw until the end, so we avoid that flash
               .Rows = NoOfRows
               .Cols = NoOfColumns
               .Row = 0            'Paste from first cell
               .Col = 0
               .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
               .ColSel = .Cols - 1
               '.Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
               .Col = 1            'Just to remove that blue selection from Flexgrid
               
               For lngRow = 1 To NoOfRows
                 For lngCol = 1 To NoOfColumns
                    .TextMatrix(lngRow - 1, lngCol - 1) = vArray(lngRow, lngCol)
                 Next
               Next
               .Redraw = True      'Now draw

Select allOpen in new window

yes.  I used one of my workbooks and it has less than a dozen rows.

This is an example of letting the user select or deselect all the items in the listbox control by way of its KeyDown() event.  People are used to Ctrl+A to select all the items.  Adding shift to that combination does the opposite (deselects all items).

Although I'm setting focus back to the listbox, you might want to set focus to another form control.  It all depends on what your user is likely to need/want to do.

Private Sub List1_KeyDown(KeyCode As Integer, Shift As Integer)
    Dim lngLoop As Long
    Dim boolSelDeSel As Boolean
    If (KeyCode = vbKeyA) And ((Shift And vbCtrlMask) = vbCtrlMask) Then
        If (Shift And vbShiftMask) = vbShiftMask Then
            'deselect all
            boolSelDeSel = False
        Else
            'select all
            boolSelDeSel = True
        End If
        List1.Visible = False
        For lngLoop = 0 To List1.ListCount - 1
            List1.Selected(lngLoop) = boolSelDeSel
        Next
        List1.Visible = True
        List1.SetFocus
    End If
End Sub

Select allOpen in new window

Well everything is working. This is great.

SiddharthRout, where you working on a code too?

Thanks for your help.

Everything is perfect now.