1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

VB application to sort a spreadsheet

Discussion in 'Software Development' started by rabbo, Oct 1, 2008.

Thread Status:
Not open for further replies.
  1. rabbo

    rabbo Thread Starter

    Jun 9, 2004

    I wrote a quick subroutine that sorts a spreadsheet based on the value in that row. It hides all the irrelevant rows.. I didn't know how to really do this, so it hides all the rows and then unhides the ones I want. See below:

    Dim rg As Range
    Dim rg_not As Range
    Dim rg_tot As Range
    Dim rg_ind As Range
    Set rg_ind = Range("a10", "f20")
    rg_ind.EntireRow.Hidden = True
    With Sheets(1).Range("c10:e16")
    Set rg = .Find(my_x)
    If rg_tot Is Nothing Then
    Set rg_tot = rg
    End If
    If Not rg Is Nothing Then
    Set rg_not = rg
    Set rg_tot = Union(rg_tot, rg)
    Set rg = .FindNext(rg)
    Loop While Not rg Is Nothing And rg.Address <> rg_not.Address
    End If
    End With
    If rg_tot Is Nothing Then GoTo rd_undo
    rg_tot.EntireRow.Hidden = False
    rg_ind.EntireRow.Hidden = False

    End Sub

    My question is this: I now want to create a dropdown menu in Excel that will have all the possible values to sort by (essentially building my own Autofilter). How can I set the sub so that it updates every time the value in the cell changes (i.e., user re-sorts)? If not, I suppose I could put the macro on a button, anyone know if that is better?

    Thanks ahead of time,

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/755335

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice