Results 1 to 15 of 15

Use macro to loop through multiple worksheets to sort data and delete rows based on data

Threaded View

  1. #1
    Registered User
    Join Date
    05-27-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Use macro to loop through multiple worksheets to sort data and delete rows based on data

    I am completely new to VBA and what I know I've learned from Google so please be patient and kind...I'm learning.

    I have several workbooks (one per campus) with multiple worksheets (one per teacher) with data that needs to be sorted by column F (LEP) and then column D (Student Name). Rows in which column F is blank or equal to "LEP" needs to be deleted.

    I have part of this figured out using:

    Sub LoopAllSheetsInOpenWorkbooks()
    Dim wb As Workbook
    Dim sht As Worksheet
    
    For Each wb In Application.Workbooks
        If wb.Name <> "PERSONAL.xlsb" Then
            For Each sht In wb.Worksheets
            
            ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("F2:F20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("D2:D20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                With ActiveWorkbook.ActiveSheet.Sort
                    .SetRange Range("A2:F20000")
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
                
            Next sht
        End If
    Next wb
    End Sub
    ______________________________________________________

    It sorts for one worksheet and looks like it is for other worksheets in a workbook (page blinks really fast) but other worksheets are not sorted.

    Also, I have no clue about deleting rows based on certain criteria. Any help would be greatly appreciated.
    Attached Files Attached Files
    Last edited by Richard Buttrey; 05-10-2016 at 12:22 PM. Reason: code tags missing

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sort and Delete Data Based on Multiple Criteria Using Check Box in Userform
    By Primas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-04-2016, 01:16 AM
  2. need to loop through worksheets and delete row data
    By AWITCHER in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2015, 04:01 PM
  3. [SOLVED] Delete rows based on the value of two columns using VBA code - Multiple worksheets
    By OmniBlue in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-18-2015, 04:24 PM
  4. [SOLVED] No Macro Experience - Need to run a loop (I think?!) over a set of data for multiple rows
    By vkelly in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-29-2014, 08:11 AM
  5. [SOLVED] Delete rows based on multiple criterias, copy/paste data and formulas
    By BQuek in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2013, 01:36 PM
  6. Loop macro for multiple separate graphs from rows of a large data set
    By GlennToms in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-30-2012, 02:12 PM
  7. Delete multiple rows of data based on criteria in a single cell
    By slaga9 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2011, 03:41 AM

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