Excel: Macro Help

Hai

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

Dear sir,


I Want macro commands.I have one dout.one macro program is assembled for 34 sheets.but now i want only 10 sheet how can i change?

Hi

I have an excel worksheet 17165 rows long and column B is 'Years'. B2 is where the spreadsheet starts and is 2006; the years then after repeat in the following order:

B2 2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2006 Here the cycle repeates starting at 2006 again.
2007
2008
2009

I need to add 11 rows below each 2009 for a total of 12 2009 (1 for each month). But only for the year 2009
What I need to do is write a code so that the macro runs trhough all 17165 rows and if it sees 2010 in column B it will add 11 blank rows above it. I am not familiar with excel programming. If possible please write a code for this and send it to me.

Thanks