This is a discussion on please help with a simple macro within the Excel Questions forums, part of the Question Forums category; i have 257 customer id numbers. each number has seven digits. they all fall inside a range from 2,200,000 to ...

i have 257 customer id numbers.
each number has seven digits.
they all fall inside a range from 2,200,000 to 3,300,000.

there is no order to them.
and when sorted, they are in no way sequential.
they are simply random 7-digit numbers inside this range.

these numbers are always located in Column F.
they will always be mixed in with other numbers in Column F,
so that if Column F is say, 2500 lines long, these specific customer ID numbers
will be scattered through the column in no particular order.

is there anyone who might help me with a macro that would
remove the entire line on the spreadsheet, where one of my customer ID numbers does not exist in Column F, and keep the entire line where it does ?

i'm really not sure if this requires a macro, or if there is a simpler way,
but all i need to do here is identify these specific customer ID numbers in Column F; keep everything on that line; and remove everthing else.

i do appreciate any and all comments or solutions.

thank you,
Jck

2. Euhm, what I understand of your problem is that you want to find a a Customer ID that doesn't Exist in column F and delete the row of that customer....
but if it doesn't exist, you won't find it for sure...

3. Originally Posted by Mat
Euhm, what I understand of your problem is that you want to find a a Customer ID that doesn't Exist in column F and delete the row of that customer....
but if it doesn't exist, you won't find it for sure...
i want to clear the contents of every line in the spreadsheet
that doesn't have one of my customer ID numbers in
column F.

PURPOSE: The following procedure evaluates ColF of the ACTIVESHEET. IF a value that does not fall within the numeric range of 2200000 to 3300000 is found THEN the entire row is removed.

Public Sub DelIfNoID()
For Each c In Range("F:F").SpecialCells(xlCellTypeConstants, 3)
If Not IsNumeric(c.Value) Then
c.ClearContents
Else
If c.Value < 2200000 Or c.Value > 3300000 Then
c.ClearContents
End If
End If
Next c
Range("F:F").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

ASSUMPTIONS:
1. The sheet to work on is the activesheet
2. values are to be found in colF of active sheet

Another thing you could do is.... Use a filter.
Your column F needs to have a column heading/name. Select that heading and go to your menu bar and select the "DATA" menu, Choose Filter>>>AutoFilter.
The heading your curser was at will now give you a drop down menu. Use that to choose (custom). You will get a popup window. In there you will see four more dropdowns. Use the first one and choose "Greater Than". To the right of that in the next drop down type in the smallest number in your required list "2,200,000". Below there you should see two radio buttons. Be sure the "AND" button is checked. Now in the third dropdown on the lower left choose "Less Than" and in the fourth dropdown type in your higher number "3,300,000". So now when you click "OK" all the numbers that fall between those two numbers will be left along with the entire rows that they are in. All other rows with unwanted numbers are still there, they are just hidden.
So you can then select all the rows that are left and copy them to a new location. or delete them, whatever. To return your data list to the way it was simply use the dropdown menu at your "F" column heading and choose "ALL".

There's all sorts of good things you can do with that simple filter.

Izzy,
that made a lot of sense, and i see how it would work, if these numbers weren't so random.

i was thinking it needed to be a macro because i have 257 specific numbers.
the fact that they fall inside a particular range is secondary.

quick example:

1F is 2249872
2F is 3390388

3F is 2900387

line 3 should be deleted since it's not one of these specific customer ID numbers,
even though it falls in the range.

Jck

TO INSTALL MY CODE :
1. Select the sheet you want code to work in
2. right click the the "Name Tab" of sheet
3. Select "View Code" in drop down menu
4. VBE window will open ... paste code in and exit VBE

TO RUN MY CODE:
1.While the sheet to evaluate is active goto tool bar
2. ON tool bar goto "Tools...Macro....Macros"
3. A list of Macros should appear
4. To run the above Macro Double Click on it's name "DelIfNoID"

Jcksht:
Since you last post I realizing there must be some modification to my code. My code keeps ALL numbers that falls within the mentioned range. I can solve you problem if you have a list of the 250 Empl numbers in a sheet . For example if you have a xls sheet called "EmplNum" wher the valid numbers are in ColA... Does such a list exist ?

9. Originally Posted by Nimrod
Jcksht:
Since you last post I realizing there must be some modification to my code. My code keeps ALL numbers that falls within the mentioned range. I can solve you problem if you have a list of the 250 Empl numbers in a sheet . For example if you have a xls sheet called "EmplNum" wher the valid numbers are in ColA... Does such a list exist ?
Nimrod,

i do indeed have a list of the 257 customer id numbers, and i can create a sheet where the valid numbers are in Col A.

Jck

10. Nimrod,

i have the list ready.
please tell me how i can post it here without taking up a lot
of bandwidth.

thanks,
Jck

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•