Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows from multiple Sheets.
Howdy:
I have 100,000+ lines of data in two sheets (Sheet1 and Sheet2) I want to delete all Rows that have a value in Column A that matches a list of Values in Column A of Sheet 3. The macro could be run on each sheet individually or every sheet at once , except "Sheet3". TIA DrBobsled |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows from multiple Sheets.
If you have MS Access then just export the data into Access, in two tables.
One table is all of the transaction data (100,000 rows). The other is the values you want to keep. Inner join the two tables and the required records will just pop out in a query. This will work a heck of a lot faster than Excel will. If you want the VBA solution that is a whole pile more work and will execute very slowly... For a one time solution you could use the Vlookup function in Excel to tag all of the keeper rows. You could sort those to the top and delete the rest of the rows. Save the file intermittently as you work because Excel is prone to crashing on files of this size... HTH "drbobsled" wrote: Howdy: I have 100,000+ lines of data in two sheets (Sheet1 and Sheet2) I want to delete all Rows that have a value in Column A that matches a list of Values in Column A of Sheet 3. The macro could be run on each sheet individually or every sheet at once , except "Sheet3". TIA DrBobsled |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows from multiple Sheets.
Hope this will work for you:
Sub Macro1() Sheets("Sheet3").Activate Range("A1").Select Do Until ActiveCell.Value = "" TextSheet3 = ActiveCell.Value Sheets("Sheet1").Activate Range("A1").Select Do Until ActiveCell.Value = "" SkipLoop: If ActiveCell.Value = TextSheet3 Then z = ActiveCell.Row Rows(z).Delete Else End If If ActiveCell.Value = TextSheet3 Then GoTo SkipLoop ActiveCell.Offset(1, 0).Select Loop Sheets("Sheet2").Activate Range("A1").Select Do Until ActiveCell.Value = "" SkipLoop2: If ActiveCell.Value = TextSheet3 Then z = ActiveCell.Row Rows(z).Delete Else End If If ActiveCell.Value = TextSheet3 Then GoTo SkipLoop2 ActiveCell.Offset(1, 0).Select Loop Sheets("Sheet3").Activate ActiveCell.Offset(1, 0).Select Loop End Sub Thanks, "drbobsled" wrote: Howdy: I have 100,000+ lines of data in two sheets (Sheet1 and Sheet2) I want to delete all Rows that have a value in Column A that matches a list of Values in Column A of Sheet 3. The macro could be run on each sheet individually or every sheet at once , except "Sheet3". TIA DrBobsled |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows from multiple Sheets.
Without trying this I would add a couple of things. Turn screen updating off
at the beginning, and back on again at the end. This will speed things up. if you have any calculated fields turn calculations off and back on again also. On 100,000 lines, if you are looking to delete 10 different items, this will loop 1,000,000 times which will take a while. Be sure to make a backup copy before you run this. There is a good chance that Excel will run out of memory and crash... "David" wrote: Hope this will work for you: Sub Macro1() Sheets("Sheet3").Activate Range("A1").Select Do Until ActiveCell.Value = "" TextSheet3 = ActiveCell.Value Sheets("Sheet1").Activate Range("A1").Select Do Until ActiveCell.Value = "" SkipLoop: If ActiveCell.Value = TextSheet3 Then z = ActiveCell.Row Rows(z).Delete Else End If If ActiveCell.Value = TextSheet3 Then GoTo SkipLoop ActiveCell.Offset(1, 0).Select Loop Sheets("Sheet2").Activate Range("A1").Select Do Until ActiveCell.Value = "" SkipLoop2: If ActiveCell.Value = TextSheet3 Then z = ActiveCell.Row Rows(z).Delete Else End If If ActiveCell.Value = TextSheet3 Then GoTo SkipLoop2 ActiveCell.Offset(1, 0).Select Loop Sheets("Sheet3").Activate ActiveCell.Offset(1, 0).Select Loop End Sub Thanks, "drbobsled" wrote: Howdy: I have 100,000+ lines of data in two sheets (Sheet1 and Sheet2) I want to delete all Rows that have a value in Column A that matches a list of Values in Column A of Sheet 3. The macro could be run on each sheet individually or every sheet at once , except "Sheet3". TIA DrBobsled |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
Complex sheets - how can I delete rows without destroying formulas? | Links and Linking in Excel | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
How can I delete similar rows in excel workbook with many sheets? | Excel Worksheet Functions | |||
Delete multiple rows | Excel Programming |