Closed Thread
Results 1 to 6 of 6

Auto Filter and Auto Format

Hybrid View

ryannjohnsonn Auto Filter and Auto Format 10-03-2012, 06:23 PM
Artik_PL Re: Auto Filter and Auto... 10-03-2012, 07:13 PM
ryannjohnsonn Re: Auto Filter and Auto... 10-03-2012, 07:24 PM
Artik_PL Re: Auto Filter and Auto... 10-03-2012, 07:50 PM
ryannjohnsonn Re: Auto Filter and Auto... 10-04-2012, 06:30 PM
arlu1201 Re: Auto Filter and Auto... 10-15-2012, 06:31 AM
  1. #1
    Registered User
    Join Date
    08-24-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    17

    Auto Filter and Auto Format

    Hello! I have another quick question!

    I have a large sheet imported from another program that varies in length from 30,000-80,000 rows. I'm making a macro that will completely format it for me in a way that is understandable rather than spending an extended amount of time each month setting it up.

    I want to include in my macro the ability to delete any row where Column O isn't equal to "CHECK", Column I is less than 69, Column F is less than 1, and Column H is not equal to CKOT.

    I've played around with this, and anything I do goes line by line, which as you can imagine would take forever with 80,000 rows. I assume theres a way to filter it with VBA to quickly purge the unnecessary data?

    After that data is deleted, I want to sort the data alphabetically based on Column C

    Thanks for the help!
    Last edited by ryannjohnsonn; 10-04-2012 at 06:28 PM.

  2. #2
    Registered User
    Join Date
    11-25-2008
    Location
    Poland
    MS-Off Ver
    MSO 2K3, 2K10
    Posts
    84

    Re: Auto Filter and Auto Format

    1. Create Filter on column O, with criterion "CHECK".
    2. Select and delete the filtered rows.
    3. Sort ascending column C.

    Do you need a macro for that? If so, then record them.

    Artik

  3. #3
    Registered User
    Join Date
    08-24-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Auto Filter and Auto Format

    Yes, I'd like to have this as a macro to save me time in the long run.

    I've recorded it and played around with it, but it's inefficient and doesn't account for the fact that the number of rows drastically changes from period to period.
    Last edited by jeffreybrown; 10-03-2012 at 07:26 PM. Reason: No need to quote whole posts...Thanks.

  4. #4
    Registered User
    Join Date
    11-25-2008
    Location
    Poland
    MS-Off Ver
    MSO 2K3, 2K10
    Posts
    84

    Re: Auto Filter and Auto Format

    For example:
    Line of code like:
    ActiveSheet.Range("$A$1:$O$80000").AutoFilter Field:=15, Criteria1:="CHECK"
    change on:
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=15, Criteria1:="CHECK"

    Other lines like:
    Rows("3:80000").Select
    Selection.Delete Shift:=xlUp
    change on:
    ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Artik

  5. #5
    Registered User
    Join Date
    08-24-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Auto Filter and Auto Format

    I've played arond with it a bit more to no avail. I'm looking for it to verify the range of data using something like:

    Range("K2").Resize(Cells(Rows.Count, "D").End(xlUp).Offset(-1).Row).Select
    I don't want to set it to 80,000 or any other finite number because I can't consistently say how big the file would be.

    Can anyone else help me with building this please?

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto Filter and Auto Format

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

    Thread Closed.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

Closed Thread

Thread Information

Users Browsing this Thread

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

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