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

**selixc** · 05-10-2016, 12:08 PM

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:

Please Login or Register  to view this content.

______________________________________________________

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.

**Richard Buttrey** · 05-10-2016, 12:22 PM

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Since this is your first post I'll add them for you this time but please note for the future.

Your macro only ever handles the Active Sheet which never changes. You need to process each of the sheets you identify with the sheet variable.

So try instead

Please Login or Register  to view this content.

And just another thought, but if you want to do any serious analysis with your data you should hold all your data on one sheet, not distributed across many sheets one for each teacher.
Add an extra column to a single sheet database and include the name of the teacher in the column and list all your data in a normalised table.

And if you ever need to analyse cross campus data you should go a step further and instead of many workbooks include them all in your single sheet database with an additional column to hold the campus name

**jaslake** · 05-10-2016, 12:31 PM

Hi selixc

Welcome to the Forum!!!

Please add Code Tags around your Code...it's a Forum requirement.

This snippet of Code is always looking at the

Please Login or Register  to view this content.

Replace all instances with

Please Login or Register  to view this content.

For example...

Please Login or Register  to view this content.

**selixc** · 05-10-2016, 02:41 PM

Thank you both for letting me know about the Code rule...it was my first time to post.

Changing the code from ActiveWorkbook.ActiveSheet. to .sht did the trick; thank you!

Any idea on how to delete rows based on data?

Side note: I'm keeping all my campuses and teachers separate for the purpose of sharing these files with others so confidentiality plays a role into this. I usually consolidate the data into a district file for the department's purposes.

**jaslake** · 05-10-2016, 03:38 PM

Hi selixc

Try this Code...

Please Login or Register  to view this content.

**selixc** · 05-10-2016, 03:45 PM

@Jaslake

I'm getting a Run-time error '1004': The command could not be completed by using the range specified. Select a single cell within the range and try the command again.

When selecting Debug it highlights the line: .Rows("1:1").AutoFilter

Thoughts?

**jaslake** · 05-10-2016, 04:08 PM

Hi selixc

What version of Excel are you running? Your Profile shows 2003.

**selixc** · 05-10-2016, 04:17 PM

It's 2007 now. I'll have to figure out how to update my profile.

**jaslake** · 05-10-2016, 04:34 PM

Hi selixc

I'm going to upload your Sample File with the Code embedded. The Code was developed in 2007 and performs as expected on my platform. Back at you in a few...

**jaslake** · 05-10-2016, 04:44 PM

Hi selixc

The attached is your Sample File with the Code embedded. Open the Workbook...CTRL + x will fire the Code. Works on my Platform...what say you???

**selixc** · 05-10-2016, 05:17 PM

It gave me another error.

Run-time error '1004': Cannot use that command on overlapping selections.

Debug highlights line: .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

**jaslake** · 05-10-2016, 05:40 PM

On the Sample File???

Originally Posted by selixc

It gave me another error.

Run-time error '1004': Cannot use that command on overlapping selections.

Debug highlights line: .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

**selixc** · 05-10-2016, 06:02 PM

Yes, on the sample file.

**jaslake** · 05-10-2016, 06:21 PM

Hi selixc

Honestly don't know...I get the attached after running the Code.

**jaslake** · 05-10-2016, 09:33 PM

I'd suggest you upload the offending file...I'll see if I can duplicate the issue.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Sort and Delete Data Based on Multiple Criteria Using Check Box in Userform

need to loop through worksheets and delete row data

[SOLVED] Delete rows based on the value of two columns using VBA code - Multiple worksheets

[SOLVED] No Macro Experience - Need to run a loop (I think?!) over a set of data for multiple rows

[SOLVED] Delete rows based on multiple criterias, copy/paste data and formulas

Loop macro for multiple separate graphs from rows of a large data set

Delete multiple rows of data based on criteria in a single cell

Bookmarks

Bookmarks

Posting Permissions