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

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
>

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
>

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
> >