Solved

EXCEL example needed

Posted on 2013-05-14
2
287 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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 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