Computing.Net > Forums > Office Software > Excel: Macro Help

Excel: Macro Help

Reply to Message Icon

Original Message
Name: arden625
Date: February 29, 2008 at 07:22:54 Pacific
Subject: Excel: Macro Help
OS: WinXP
CPU/Ram: AMD 3500+, 2GB RAM
Model/Manufacturer: HP
Comment:

Hello,

I need some help setting up a macro for Excel.

Situation:
I have a list of IDs/SKUs on Sheet1, and a partial list on Sheet2. What I would like to do is cross-check the two sheets and delete all the rows on Sheet1 that's already listed on Sheet2. The IDs/SKUs are on the same column for both sheets.

Any help would be appreciated. Thank you in advance.


Report Offensive Message For Removal


Response Number 1
Name: DerbyDad03
Date: February 29, 2008 at 08:52:34 Pacific
Subject: Excel: Macro Help
Reply: (edit)

The following routine is a modified version of something I posted a while back. In that case, the poster want to delete rows on Sheet1 if an ID was *not* found on Sheet2.

The comment line with the *** shows the line where the change was made to delete rows on Sheet1 if the ID *was* found on Sheet2.

Explanation:

When using Set c =.Find(item):

c will contain all the attributes of the item if it is found. e.g. c.Address will return the cell address of the item, c.Font.Name will return...well, I'm sure you can figure that out.

c will be set to Nothing if it is not found.

Now, in the VBA programmers' infinite wisdom, they decided to use TRUE as the only way to check and see if an item was found.

When "If c Is Nothing" returns TRUE, the item wasn't found.

and

When "If Not c is Nothing" returns TRUE the item was found. (I would have prefered "If c is Not Nothing", but, hey, they didn't check with me first!)

So here's the code using "If Not c" to delete rows if the item is found.

Sub DeleteIDs()
'Assume Sheet1 has long list, Sheet2 has short list
'Find last piece of data in Sheet1 Column A
LastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
'Start at bottom of Column A and get ID
For NxtID = LastRow To 1 Step -1
ID = Sheets(1).Cells(NxtID, "A")
'Look for ID in Column A of Sheet2
With Sheets(2).Range("A1:A" & LastRow)
Set c = .Find(ID, lookat:=xlWhole)
'Delete row in Sheet1 if ID is found on Sheet2
'*** Remove 'Not' to delete rows if ID is not found on Sheet2
If Not c Is Nothing Then Sheets(1).Cells(NxtID, "A").EntireRow.Delete
End With
'Move up 1 row on Sheet1 and check the next ID
Next
End Sub


Report Offensive Follow Up For Removal

Response Number 2
Name: arden625
Date: February 29, 2008 at 11:14:23 Pacific
Subject: Excel: Macro Help
Reply: (edit)

I figured I just needed to change that one line. And I did try "If c Is Not Nothing..." when I was fiddling with it before I posted and obviously, that did not work.

Thank you. It worked. :)


Report Offensive Follow Up For Removal







Use following form to reply to current message:

   Name: From My Computing.Net Settings
 E-Mail: From My Computing.Net Settings

Subject: Excel: Macro Help

Comments:

 


  Homepage URL (*): 
Homepage Title (*): 
         Image URL: 
 
Data Recovery Software




How often do you use Computing.Net?

Every Day
Once a Week
Once a Month
This Is My First Time!


View Results

Poll Finishes In 2 Days.
Discuss in The Lounge