+ Reply to Thread
Results 1 to 3 of 3

VBA to Sort, show and handle certain data in Excel 2013

  1. #1
    Registered User
    Join Date
    01-19-2015
    Location
    Chicago
    MS-Off Ver
    2013
    Posts
    2

    Post VBA to Sort, show and handle certain data in Excel 2013

    I have an excel spreadsheet and I can assign various macros but I want to do multiple scripts to sort and show specific data.

    My goal is to sort by SF greater than 50,000, Last Appraised Value greater than $5,000,000.00 and by Date Added less than 1/19/2015.

    Basically, I just want to show ONLY the data that falls within this criteria. Then, I want to be able to change this when I need which I can do in the Developer portion of Excel.

    I attached the NON-Macro based spreadsheet for the actual data.

    Thanks in advance to you all helping me learn this a bit more. Much appreciated.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-19-2015
    Location
    Chicago
    MS-Off Ver
    2013
    Posts
    2

    Re: VBA to Sort, show and handle certain data in Excel 2013

    Here is the data that is in the spreadsheet.

    State Name Last Appraised Value SF Occ. Average Rent. Date Added
    AL Property A $9,000,000.00 150,000 98% $24.00 12/1/2003
    CA Property H $12,000,000.00 178,000 90% $42.00 1/12/2013
    CO Property I $18,000,000.00 220,000 86% $28.00 1/15/2015
    MA Property E $4,200,000.00 36,000 94% $40.00 2/14/2011
    MI Property D $8,000,000.00 120,000 86% $36.00 1/18/2015
    NJ Property B $5,400,000.00 132,000 80% $49.00 11/10/2014
    NJ Property G $2,000,000.00 58,000 81% $39.00 5/2/2013
    NM Property F $3,000,000.00 40,000 85% $29.00 1/12/2015
    NY Property C $6,000,000.00 85,000 82% $56.00 9/6/2009
    WA Property J $4,000,000.00 42,000 71% $45.00 7/28/2014

  3. #3
    Registered User
    Join Date
    04-03-2014
    Location
    lima, peru
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    7

    Smile Re: VBA to Sort, show and handle certain data in Excel 2013

    Hi CJRaptor,

    I took a look at your spreadsheet and just recorded a macro to output the VBA you wanted. The macro is below. You could change the numbers in the VBA macro any way you want, by reading them from a cell or from some other location etc.

    Sub Macro1()

    Range("A1:G1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$11").AutoFilter Field:=4, Criteria1:=">50000", Operator:=xlAnd
    ActiveSheet.Range("$A$1:$G$11").AutoFilter Field:=3, Criteria1:=">5000000", Operator:=xlAnd
    ActiveSheet.Range("$A$1:$G$11").AutoFilter Field:=7, Criteria1:= "<1/19/2015", Operator:=xlAnd

    End Sub

    But I'm not sure if I answered your question, it seems too simple? Are you trying to hide the rest of the data results from your users?

    We built a program called HiveLink to allow you to create lightweight spreadsheet interfaces for your users, so that they can enter their inputs and receive the outputs but without being able to access the sensitive data, macros, or calculations that transform the outputs. I don't know your exact situation but you could potentially use it to create a spreadsheet that hides the bulk of the data and only returns the data that you want them to receive. They might not have any inputs at all, they could just request to get the outputs.

    You can check out more here: https://hivelink.io

    Cheers
    Stu

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 09-27-2014, 12:49 PM
  2. excel 2013 vba - show modal userform - can't open another .xls file
    By excel_joe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-18-2014, 01:37 PM
  3. Can't sort pivot table in Excel 2013
    By kleemc in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-06-2013, 08:55 AM
  4. Replies: 10
    Last Post: 08-20-2013, 01:02 PM
  5. [SOLVED] Sort Data not working Excel 2013?
    By nobodyukno in forum Excel General
    Replies: 6
    Last Post: 08-14-2013, 10:31 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1