Solved

EXCEL example needed

Posted on 2013-05-14
2
296 Views
Last Modified: 2013-05-14
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
Comment
Question by:Idarac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 39165604
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
 
LVL 1

Author Closing Comment

by:Idarac
ID: 39166156
Great example thank you very much.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question