Solved

EXCEL example needed

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now