VB application to sort a spreadsheet

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

rabbo

Thread Starter
Joined
Jun 9, 2004
Messages
22
Hello,

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
Do
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
End
rd_undo:
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,

rabbo
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top