[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

EXCEL example needed

I need an EXCEL VBA example of filtering a spreadsheet without using the built in filter function.

At the top of the worksheet is a dropdown with company names (
Company A, Company B, Company C.)

On the spreadsheet is a list of rows.
Company A      Winget 1
Company B      Winget 1
Company A      Winget 3
Company A      Winget 4
Company A      Winget 5
Company B      Winget 3
Company C      Winget 3
Company C      Winget 4

When a name is selected only the rows with the company name shows.
If you select Company A you will ony see these rows.
Company A      Winget 1
Company A      Winget 3
Company A      Winget 4
Company A      Winget 5
0
Idarac
Asked:
Idarac
1 Solution
 
Ken ButtersCommented:
This code inserted into Sheet 1 (where sheet1 has the data you provided above) will do it:

Note: This solution does not offer the user an obvious way to "Unfilter" the rows

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim selectedValue As String
    Dim thisCell As Range
    Dim myLastCell As Range
    
    
    If Target.Column = 1 Then
        
        ' Need to make sure that we don't go past the end of the used portion of the sheet
        Set myLastCell = LastCell(ActiveSheet)

       ' Loop through each used cell in column 1
        
        For Each thisCell In Range("A1:A" & myLastCell.Row)

           'Set the entire row to hidden if it does not match the currently selected cell

            If thisCell.Value <> Target.Value Then
                thisCell.EntireRow.Hidden = True
            Else
                thisCell.EntireRow.Hidden = False
            End If
        Next
    
    End If
    
End Sub

Function LastCell(ws As Worksheet) As Range
'
' Note "&" denotes a long value; "%" denotes an integer value
  
  Dim LastRow&, lastCol%

' Error-handling is here in case there is not any
' data in the worksheet

  On Error Resume Next

  With ws

  ' Find the last real row

    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

  ' Find the last real column

    lastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

  End With

' Finally, initialize a Range object variable for
' the last populated row.

  Set LastCell = ws.Cells(LastRow&, lastCol%)

End Function

Open in new window

0
 
IdaracAuthor Commented:
Great example thank you very much.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now