Data cleaning

[a303]

Hello everyone,

I am completely new to VBA in Excel, I haven't even written a single line of code yet. I hope I can have your help in writing a macro. This is what I want it to do:

1. Accept an array of integers (say 12, 27, 351, etc.). The size of array is not fixed.
2. Find records (rows) which have that integers in the first cell of that row (Cell An). Each cell (An) will contain only one integer (27) and not a string ("12 27").
3a. Move the entire row to a new workbook.

Alternative to step 3b:
3. Delete all rows (and shift cells up) in which the first cell does NOT contain any integer on the list I supply in the initial stages of the macro.

I want this macro to run over all the worksheets in the workbook. However, the copied rows have to be on a single worksheet. If the number of rows to be copied is greater than 65536 rows (Excel's limit), I want them (the rows) to be added to a new worksheet.

My problem is that I am working with a very large set of data, so I can't prune the data manually. Please help!

[MSP77079]

Turn on the macro recorder (Tools >> Macro >> Record New Macro). Accept whatever name it suggests (probably Macro1).

Select the range you want to search (so we can see how large it is, or how you go about figuring out what to select).

Do what you are asking for manually (use find, if it is not what you want, use find again, et cetera).

When finished, turn off the macro recorder (Tools >> Macro >> Stop Recording).

Now, find the code. To do this, use Tools >> Macro >> Macros, select your macro (whatever name from step 1), then click "Edit". This will open the Visual Basic Editor with the cursor at the macro you just recorded.

Copy everything from the word Sub to the words "End Sub".

Come back to this forum and post a reply to this thread. BUT ... before you paste the code here, press the # at the top of the window so that we can see the code in its native state.

[duane]

how do you want to enter the array of integers? can they be in a column or row (preferrably in a named range)?

[a303]

MSP, I will do as you say and report back.

Duane, I just want it to accept some numbers from me, see if the first cell of every row (in the entire workbook) contains any one from the list of numbers that I entered previously, and if it does, move that entire row to a new worksheet/workbook.

[duane]

the easiest way for me would be to dedicate some range - either a column or a row in which to enter the integers you want to search for, and then give this range some name such as criteria, or whatever you prefer. Then it is pretty straightforward.

[MSP77079]

Here is an example of implementing duane's suggestion.

First, back-up your workbook. Then, either copy a few sheets from it to the attached workbook; or, if really bold, copy the sheet named List (that sheet only) from the attached to a copy of your workbook.

When I say "copy a sheet", I DO NOT mean to copy cells. I mean to right-click on the tab, select "Move or Copy" from the context dialog, select the workbook to copy to from the drop-down list, and BE SURE TO check the box for "Make a copy".