Solved: Match one worksheet with another worksheet

[Shazam]

Hello eveyone,


I attach a sample workbook below. I have 2 worksheets one is name List and import. I import data from the database into the import worksheet. But I dont need all that data. So I created a list the ones I do want. So can the code look in column C in import and if it dont match in the worksheet List then delete the entire row. I created another worksheet that is named Results to give you a better visual.

[mvidas]

Hello,
Give the following a try, should do what you need. The results were different than yours, since yours are sorted with a blank line between each part#. Did you want me to include this functionality in it?[vba]Sub SHAZAM()
Dim i As Long, CLL As Range, ImportWS As Worksheet, ListWS As Worksheet
Set ImportWS = Sheets("import")
Set ListWS = Sheets("list")
Application.ScreenUpdating = False
For i = ImportWS.Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
Set CLL = Nothing
Set CLL = ListWS.Columns("C").Find(ImportWS.Cells(i, 3), LookIn:=xlValues, _
LookAt:=xlWhole)
If CLL Is Nothing Then
ImportWS.Rows(i).Delete
End If
Next
Application.ScreenUpdating = True
End Sub[/vba]Matt

[austenr]

Hey ya Matt,

Can't get this to compile. The error is on Rows.Count. Thought you would like to know.

Austen

[mvidas]

Thanks Austen ... not sure why that would happen for you though. Could you be compiling it in a different VBA than excel? As written, even without an activeworkbook, it should still compile without error if done in excel.. though to be thorough I should have used ImportWS.Rows.Count (really shouldn't have made a difference though).
Do you remember what error you got on that line was?

[austenr]

Expected function or variable

Austen

[mvidas]

Hmmmm.. i dont know what to tell you! Should be fine as is, did you still get that error using ImportWS.Rows.Count instead?

[austenr]

Yes. Compiling using Visual Basic 6.3

[mvidas]

Hmmm.. if you're compiling in straight VB, you may have to qualify everything there to have a sheet/application reference. Another thing might be the use of the constant xlUp. You may want to try using .End(-4162) instead
Full code (hopefully), everything should be qualified on here:[vba]Sub austenrForVB63()
Dim i As Long, CLL As Object, ImportWS As Object, ListWS As Object, xlApp As Object
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True
xlApp.Workbooks.Open "C:\workbook.xls"
Set ImportWS = xlApp.Sheets("import")
Set ListWS = xlApp.Sheets("list")
xlApp.ScreenUpdating = False
For i = ImportWS.Range("C" & xlApp.Rows.Count).End(-4162).Row To 2 Step -1
Set CLL = Nothing
Set CLL = ListWS.Columns(3).Find(ImportWS.Cells(i, 3), LookIn:=-4163, LookAt:=1)
If CLL Is Nothing Then
ImportWS.Rows(i).Delete
End If
Next
xlApp.ScreenUpdating = True
End Sub[/vba]This wouldn't be necessary for you, Shazam, only for someone using vb

[austenr]

Sorry Matt, I should have told you I was compiling in the VBE in EXCEL 2003.

[mvidas]

Then I definately have no idea

[austenr]

Im going to send you my file.

[mvidas]

Ahh, that makes more sense I had opened up Shazam's example file, and deleted the 4 modules inside it, as I wasn't using them. I inserted a new module, and created my code.

Shazam has a macro in his Module2 named "Rows", which is where your error is coming from. Either rename that macro or delete it, you're getting the error because he named it the same as a built in excel object

Thanks, I didn't think to try it in his original file, and was going a little crazy there trying to figure out why you'd get that compile error

[austenr]

Sorry to cause you so much aggrevations!!!

[mvidas]

No worries, I'd rather be challenge and get aggrivated than unchallenged and bored

[austenr]

Funny me too. BTW, I tried the macro and it did not delete anything.

[mvidas]

Did you use my original one, or the austenrvb63 one? You should be using my original one, the other wouldnt work as described unless you had the workbook at c:\workbook.xls and you made the app visible (something I just added to it)

[Shazam]

Thank mvidas! For awhile I thought i was not going to get a response but thank you for responding.

Thanks!

[austenr]

Hi Matt,

I take back what I siad that it did not delete rows. Was looking at my test version before your changes. LOL!!

[mvidas]

Sorry about the delay, shazam, maybe your question disappeared for a little bit?