Deleting rows based on a certain columns 'prefixes'?

**Mgassma** · 07-01-2013, 03:13 PM

For one of my columns within an excel spreadsheet, I have department numbers that consist of five digits.
I am hitting a roadblock in writing a macro that will delete any row containing department numbers that begin with a certain combination of 2 numbers.

Example:

Dept: \ Hours:
10301 \ 134.11
10303 \ 45.10
10310 \ 169.05
10311 \ 37.69
11332 \ 4.50
11363 \ 433.12

Would need to delete rows containing those starting with an 11 underneath the department column, without effecting other departments or the hours column

In addition, is there any type of macro that, after deleting those numbers starting with 11, would also delete all of the other 'prefixes' (so delete the first two numbers on all of the departments?)

Thank you so much.

(Sorry, I cannot provide a sample workbook, due to using this for sensitive timesheet information within my company)

**stanleydgromjr** · 07-01-2013, 04:19 PM

Mgassma,

Welcome to the Excel Forum.

You have not given us enough information.

1. What is the worksheet name?

2. What column contains the title Dept:?

3. What cell is the title Dept: in?

4. What is the first row that contains raw data?

I have a macro ready to go, but, I will need the answers to the above 4 questions.

**Palmetto** · 07-01-2013, 04:28 PM

Try this on a back up copy and see if it meets your needs. Reference to Sheet1 is to the sheet code name - change it as needed.
This assumes values in first column (A) are always 5-digits.

Please Login or Register  to view this content.

**Mgassma** · 07-02-2013, 08:45 AM

Palmetto -

Thank you, but is there a way I can do it without having to enter the code every time? I will always be deleting those that don't start with a 10.

**Mgassma** · 07-02-2013, 08:51 AM

StanleyDGromJr -

I would have been perfectly able to change any of that information from the core macro, but:

1 - "Timecard"
2 - The title is actually "Worked Department", and is in column A
3 - The label is "Worked Department" is in A1
4 - Raw data starts in the second row

**Palmetto** · 07-02-2013, 09:13 AM

Here is the code revised, no input box, delete all rows that don't start with "10" and strips first two digits from department.

Please Login or Register  to view this content.

**Mgassma** · 07-02-2013, 09:23 AM

Nevermind, I've solved my problem. I have switched some steps around, used a code that changes the applicable data from the original 5 digit codes to the four digit codes that are needed, and ended up writing a macro that just deleted the rows containing numbers in the proper column that were greater than four digits.

Thank you for all of your help, however.

**stanleydgromjr** · 07-02-2013, 03:40 PM

Mgassma,

If you had given us all the information we needed from the beginning, your request would have been solved the first time around.

Thanks for the information.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the DeleteRowsAndPrefixes macro.