how to delete rows?

[Cranium]

so i have this list of numbers...

1001
1002
1003
1004
1005
etc.... (not in numerical order)

and i have a bunch of rows in my excel sheet. i want it to look in column F for these values, and I want it to delete any entire rows that don't contain one of these 64 numbers in column F.

can someone help a newb with the marco script to accomplish this?

[lucas]

Are you saying that if the cell in column F is blank to delete the row?

[mdmackillop]

Hi Cranium,
Where is your list of numbers stored?

[Cranium]

no, i'm saying if the content inside the cell in column F is NOT one of those numbers, then delete the entire row.

[Cranium]

i will store the list in the macro itself.

[mdmackillop]

This makes use of column A. Modify Cells(1,1) to a suitable location and the match function address to suit

Sub DelRows()
Arr = Array(1001, 1002, 1003, 1004, 1005)
Cells(1, 1).Resize(UBound(Arr) + 1) = Application.WorksheetFunction.Transpose(Arr)
Columns(7).Insert
Columns(6).SpecialCells(xlCellTypeConstants, 1).Offset(, 1).FormulaR1C1 = "=MATCH(RC[-1],R1C1:R64C1,0)"
Columns(7).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Columns(7).Delete
Cells(1, 1).Resize(UBound(Arr) + 1).ClearContents
End Sub

[Cranium]

can u elaborate on "Modify Cells(1,1) to a suitable location and the match function address to suit". not sure what u mean.

also, if these numbers and macro are stored in a workbook separate from the workbook i want to modify, does this still work? i dont want the macro and numbers stored in the workbook i want to modify.

[mdmackillop]

OK
If you have data in Column A, you can't put data there. You could use column Z, but allow for the extra column inserted by the code

Sub DelRows()
Arr = Array(1001, 1002, 1003, 1004, 1005)
Cells(1, 26).Resize(UBound(Arr) + 1) = Application.WorksheetFunction.Transpose(Arr)
Columns(7).Insert
Columns(6).SpecialCells(xlCellTypeConstants, 1).Offset(, 1).FormulaR1C1 = "=MATCH(RC[-1],R1C27:R64C27,0)"
Columns(7).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Columns(7).Delete
Cells(1, 26).Resize(UBound(Arr) + 1).ClearContents
End Sub

[Cranium]

i want the data and macro in a separate reference xls file, because this is going to have to be applied to various worksheets, once a week and i dont want to have to copy it each time.

[mdmackillop]

Put it in your Personal.xls

[Cranium]

so the code you gave me will still work? whats personal xls? is that a built in macro xls file? sorry i sound retarded. ive been using excel for years, but never macros.

[Cranium]

can i email you the xls file with the numbers and then u stick the macro in it that can edit the active workbook (not the marco xls file)?

[mdmackillop]

To create a Personal.xls
Open a workbook
Tools/Macros/Record new macro
Macro name: Macro1
Store macro in: Personal Macro Workbook
OK then Stop recording

Alt + F11 to open VB editor
Open vbaProject Personal.xls/Modules/Module1 (double click)
Paste the code in the Code pane on the right.
Save then Alt + F11 to close

Alt+ F8 to see the list of macros. You should see DelRows listed.
Running it will run the code on the active worksheet, in whatever workbook is open.

[Cranium]

YES it worked! thanks so much man!