Delete Contents From Multiple Rows

Dec 29, 2012

I'm trying to put together functionality which allows the user to remove cell content within a selected row. I've chosen to remove the cell content rather than to delete the whole row because I need to maintain the 'Input Range'.

The code below is the script which I've put together to clear the cell contents.

Code:
Sub DelRow()
Dim msg
Sheets("Input").Protect "password", UserInterFaceOnly:=True
Application.EnableCancelKey = xlDisabled
Application.EnableEvents = False
msg = MsgBox("Are you sure you want to delete this row?", vbYesNo)

[Code] ........

The code works fine if the user selects one row. However, if the user selects mutliple rows, although the text values are removed from all rows, only the 'Interior Color' is removed from the first rather than all and I'm not sure why.

View 3 Replies


ADVERTISEMENT

Delete Rows Missing Contents In AF Then A

Jul 23, 2009

If a row in Column AF does not say “Cust” than I want to delete it, next if a row in Column A does not say “Numb” I want to delete it.

View 3 Replies View Related

Move Cell Contents & Delete Rows

May 23, 2006

I have a spreadsheet with a serial number is row 1 in column A with the rest of Row 1 empty. Column A is empty in Row 2, but has the data associated with the row above in columns B through L of Row 2. Then comes 2 blank rows and the pattern repeats with a new serial number in column A of row 5 and so on. I would like to move the serial number down one row, delete the now empty first row, delete the following 2 empty rows, and then loop to do the same thing again for all 9000 rows of the spreadsheet.

View 2 Replies View Related

Delete Rows Based On Cell Contents

Aug 29, 2007

I have merged two workbooks into one. What I need to do at this point is to delete all rows that have a duplicate entry, basically anytime the cell content in one cell matches the cell content in the cell right below or above it, BOTH rows should be deleted. At this point, this is above my VBA skills so I'm asking for help in how to do this. The stripped version of the workbook is attached (only 100 rows) but in reality this is a huge workbook with almost 22,000 rows.

You will notice in the attached workbook, that cell contents for A2 and A3 match. For what I need to do, I need both rows (2 and 3) to be deleted. If you go down a bit, starting in row 89 all the contents in column A are unique so those need to remain.

View 3 Replies View Related

How To Clear Contents / Delete Rows On Click Of Button

Nov 3, 2013

I have a spreadsheet containing 100 "requests" which can be made by a user

i would like the option for the user to be able to delete a request should they wish it either by deleting a row or clearing the contents

Currently I have a single column which is full of buttons (one for each row)

when i click the button on row 10 I would like row 10 deleted

when i click the button on row 30 i would like row 30 to be deleted etc

is there a way to do this without writing 100 macros which are specific to each line?

View 4 Replies View Related

Modify Macro In Excel / Transpose - Delete Contents - Skip X Rows - Repeat?

Mar 7, 2013

I have a list in Excel, and it has the company in one column, and it's information in the next x rows until there is a blank row (4-7 rows). I get that you can copy the rows under the Company, transpose next to the company, and then delete the contents of the cells that you just took the data from, but I have 6200 rows do to this to, for 500+ companies. I tried recording a macro for two entries, and this is what I have (see below), but how do I modify this so that I don't need to type in every single range, and it will do it to the whole column? The data is in column A, I am posting in column B.

Basics for Macro Requirement:

1. Find the blank cell in row A
2. Skip the next cell/row (this is the company)
3. Select all the cells beneath the company cell, until it reaches the blank cell underneath
4. Copy, transpose these cells next to the Company cell (transpose in column B)
5. Delete the contents in row A that were just transposed
6. Find the next cell with data (company)
7. Repeat Steps 2-6

My recorded Macro:

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("A3:A8").Select

[code]....

View 5 Replies View Related

Copying Cell Contents From One Column To Multiple Rows

Mar 20, 2014

I'm sure there is an easier way than copying and pasting each individual cell from one spreadsheet to another. Is there a way I can define a batch of cells (city, state, phone #) and copy them into the other spreadsheet so I don't have to do each cell individually?

Here is a picture to show what exactly I am trying to do.

SS.PNG

Also, the cells that belong in the same column and row on one spreadsheet are equal distance from each other throughout the other spreadsheet that has all the info in 1 column.

View 1 Replies View Related

Macro To Delete If Set Of Multiple Rows Equals Another Set Of Rows

May 10, 2012

I need a macro that can look at multiple (say three) rows and delete those rows if they match another three rows in the worksheet.

For example:

1 0
3 1
5 7
4 4
6 5
8 3
1 8
5 2
3 9
6 5
8 3
1 8
7 5

If three rows are the same, then one set is deleted (it can be either bottom or top set). I would like to do this for rows 1-500 in the worksheet.

View 1 Replies View Related

Delete Rows Where Multiple Column Meets Multiple Criteria

Sep 29, 2011

Need to create a macro?

Delete rows where multiple column meets multiple criteria.

detail:
delete rows where
Column H is less than 10000
AND
Column C is empty(blank)

Those 2 criteria have to occur at the same time..

View 1 Replies View Related

Delete Multiple Rows :: Match In Multiple Columns

Oct 29, 2008

I need to find all rows that have columns that match in all 3 of the columns.

I then need to delete all but the last row in each "group" of rows.

like:

1 q w e
2 q w r
3 q w r
4 q w r

Delete rows 2+3

View 9 Replies View Related

Delete Rows With Multiple Criteria

Sep 8, 2009

i have several student ID results that have conditional formatting in place. I'd like to KEEP the following data but delete/hide everything else:

1) Filter out purple rows
2) WITH filtered rows: KEEP any scoreA that's 100% or less than 85%
3) Look at Score A and Score B - KEEP if the difference is greater/less than 5%
4) If a student took testB but didnt take testA, KEEP.

View 2 Replies View Related

Delete Rows, Multiple Conditions

Jul 20, 2009

I need help with the following conditions. I have attached a sample file with desired results.
Conditions required to delete row:

1Delete Row when cell E is blank (example: Row 6 will be deleted as E6 is blank)
2First character in column 'C' is an alphabet (for example: Row 5 will be deleted as the first character in the cell is an alphabet
3First character in column 'B' is an alphabet (for example: Row 29 will be deleted as the first character in the cell is an alphabet

Special Notes : All characters in column 'B' are not in number format (ex: 555-9999 is general)
There are more than 1500 rows of data that need to be sorted with the above conditions.

View 9 Replies View Related

Delete Rows With Multiple Space

Nov 16, 2009

I need a macro to delete rows with spaces greater than 20. I don't know how excel define a space so I'm not sure where to start.

View 4 Replies View Related

Delete Rows On Multiple Sheets Below

Apr 22, 2007

One more short macro code I would like to have.

I would like to delete rows on all worksheets in a workbook below where the value "Total" exists in column B (same on all sheets).

View 9 Replies View Related

Delete Multiple Rows Under Certain Conditions

Aug 1, 2008

I need to eliminate the passing jobs, leaving the failing ones so I can do coaching sessions with the guys that are missing the mark.

Conventions of the report:

Each job has a 20 digit job # and is allocated 12 rows worth of information fields unless customer has more than 1 cable outlet with a modem, then the rows increase by 4 for each additional modem
Tech name is repeated after every 12 rows even if within the same job #
On passing jobs, column I will only contain “Yes” (there are blank cells in column I, as not every parameter is checked on every job), failing jobs will contain “No” in column I

Items I need to accomplish:

· Increase column C width to 22 and column E to 25

· Format column C to number, 0 decimal places (to avoid scientific notation)

· Move tech name to beginning of each job # and eliminate repeated names

· Delete all rows plus tech name on passing jobs

· Change cell color to red in rows (columns A-I) that contain “No” in column I

I have highlighted each different job # in different color


John Smith10002934422610200000Outlet 2D - Up Stream SNR274029.4Yes10002934422610200000Outlet 2D - Up Stream Tx365546Yes10002934422610200000Outlet 2D - Down Stream .............................

View 10 Replies View Related

VBA To Insert And Delete Multiple Rows

May 30, 2009

Does anyone have codes to insert and delete multiple rows. I need to run a macro where a dialog box pops up requesting number of rows to insert and delete.

View 9 Replies View Related

Macro To Delete Rows Containing Multiple Criteria?

Feb 23, 2011

how they can be used to delete rows that meet multiple criteria, however all the examples I have found seem to be rather complex and personal to the individuals as opposed to being rather generic. Many people, including myself would find it useful to have a simplified generic answer that can be applied to all kinds of criteria. Such as if the data in column B = x and the data in column C = y delete the row. Here is my example.

SCENARIO EXAMPLE:

In column A - Date e.g. 05/02/2011, 06/02/2011
In column B - Day
In column C - Time (in format 09:06:21)
In column D – Service numbers dialled (70, 110, 224) two to three digits long

MACRO EXAMPLE:

Delete the row if the criteria in:
Column D = 10, 40, 192 or 244
And
Column C is between 09:00:00 and 16:00:00

macro code or at least a link to a thread that they think gives a scenario as simple as this.

View 5 Replies View Related

How To Delete Multiple Rows But That Match In Columns

May 22, 2014

ertret.jpg

I am trying to delete rows that contain the same addresses BASED ON COLUMNS.

Do you see how in columns C and D they all match EXCEPT for row 4? How do I delete allllll the rows that when c and d match.

I want to keep the ones that DO NOT MATCH.

P.S I HAVE 4000 ROWS I WOULD HAVE TO GO THROUGH

View 1 Replies View Related

Delete Rows Based On Value In Multiple Sheets?

Apr 2, 2014

I have the following code to delete rows based on the value "No" in cloumn "L". This code should loop through all sheets and delete the corresponding rows.

[Code]....

The code works perfectly but with one little issue. It fails in the first run always with the following error message.

VBA error.png

I need to comment out the line

[Code] ....

continue it with a breakpoint to "End With", remove the comment. Then it loops all worksheets and deletes the rows.

There is no protection or similiar on any sheet.

View 1 Replies View Related

Delete Rows Multiple Criteria W/Numbers

Dec 23, 2009

I am trying to use the code below to eliminate data in a spreadsheet based on certain criteria. I have been able to get this to work with text fields just fine, but now I am running into an issue with numbers. I am trying to get this code to delete rows if they have the word "unknown" in column C and if the value in column B is greater than 900 hours (999:00:00), the numbers in this column are formatted as [hhh]:mm:ss. What should I tweak to make this work?

View 7 Replies View Related

Delete And Sum Duplicate Rows With Multiple Condition

Oct 31, 2012

Here is the problem: i have two conditions to satisfy first is data from column A and second is data from column C, what i wanted to do is if both data from column A and B has the same other data in column A and C then sum the total in column D, F, I retain data for column B, E, G and H. Then delete the duplicate rows. By the way we also need to replace the remove the text (W1,W2,W3,W4 and W5) in column C.

View 6 Replies View Related

Delete Multiple Rows But Not With Max Volume Of Business

May 8, 2007

I am currently doing some work which is taking ages and i wonder if there might be a quicker way of completing the work.

Each Row contains a User ID alone with the volume of business submitted under that user ID via a specific payment route, the problem i have is that i may have 10 of the same user ID's but each with a different business volume and payment route attached, I have already sub totaled by User ID so tat can see total business volume for each user however, I want to delete all the duplicate user IDs leaving only the row with the max volume of business attached.

I have approx 40,000 user ID's so i need the solution look for sets of User IDs and leave only the one with the biggest volume of business attached to it.

View 9 Replies View Related

Delete Multiple Rows Protected Worksheet

Apr 4, 2007

Is it possible to delete multiple rows when the worksheet is protected using vba? For Example Peps - Select a undetermined amount of rows and then use a macro to unprotect - delete the selected rows - protect.

View 5 Replies View Related

Delete Rows Not Meeting Multiple Criteria

Feb 29, 2008

I have an extremely large CSV file that I am opening via Excel. Can anyone tell me how to delete a row using multiple criteria. For Example, i want to delete the whole row if a certain cell doesnt equal GA SC or NC. I have seen how it can be done using one criteria but not multiple ones.

View 8 Replies View Related

Identifying Rows To Delete Based On Multiple Conditions?

Jun 5, 2014

Maybe this has been taken care of in another thread, but still:

ROW ID NUM OCC K OCC I
1 222222 0 0
2 222222 0 0
3
4 333333 0 0
5 333333 0 0
6 333333 0 0
7
8 444455 1 1
9 444455 1 1

I have a situation like above, but what I want to do is:

=FOR(ROW1:ROW9, IF COUNT ROWS(ID_NUM)>= 2,

(create name for set?)

AND

IF OCC_K AND OR OCC_I VALUES >= 0,

THEN DELETE ROWS(within set) >=2

Or more simply:

If there are rows with multiple Accident ID numbers, name that set,
and if OCC_K and/or OCC_K values are BOTH >= 0,
then delete all duplicate rows?

Yes I figure there needs to be some way to distinguish Row 1 from Row 2, (flagging one of the two?) so that ROW 2 can be identified, and similarly with Row 4 from Row 5 from Row 6, but the general flow of commands is what I'm trying to get at.

Is it also possible to use AND and OR in the same line? Or BOTH?

(I have other variations on this that I also like to ask about, such as multiple rows within a set that meet certain conditions that are identified in a different field, but for now this is a simpler version.)

I would like to think I'm not going to need a macro, and that I can do this in a Formula.

View 10 Replies View Related

Macro To Delete Rows Based On Multiple Criteria?

Jul 10, 2014

A macro is required to identify rows within a selection e.g. entire column A, that share the same value, then delete appropriate rows depending on the values in another column. The attached example details the requirements.

Extract Rec1.xlsx

View 6 Replies View Related

Macro Button To Add / Delete Rows Across Multiple Sheets

Jul 24, 2014

I've been piecing a macro together from different sites and have run into a couple issues I can't find resolutions to. I have a excel doc for tracking paint emissions per week. I would like to have a button on my master tab that will add a row and fill the formatting from the line above across all 60 some sheets. Ideally I would like the same button to first prompt if you want to add or delete rows. When adding rows I would like it to add it above the button (there's 3 buttons and for 3 different sections) and if deleting ask which row to delete. If it can't be done in one button that's fine, I can live with that. The big thing I need is for it to replicate across all of the excel sheets. I've attached a copy of the document

View 1 Replies View Related

Macro To Search Multiple Values And Delete Rows.

Nov 5, 2008

How to create a macro to search a value and delete the row but I'm having trouble trying to get this to search multiple values. Keep in mind I'm really new at attempting to create a macro so this may look terrible ;-)

I have a list of 20 numbers (changes from time to time) and I need to filter any row containing any one of these numbers out of my results each day. I am currently able to filter a single group of numbers but get an end error every time I attempt to string a group of numbers.

This is what I have so far and is an example. We'll use three numbers as an example.

"12345","12346","12347" are the numbers that we'll use for the example that I need to filter. The code that I have is:

View 13 Replies View Related

Delete Rows With Specific Text (multiple Possibilities)

Nov 13, 2013

I need a VBA macro that deletes whole rows if the cell in one of the columns does not match one of three texts. For example, in column B, if the text in there isn't "Mary", "Joseph", or " " (empty space), the row must be deleted.

View 4 Replies View Related

How To Delete Rows With Strikethrough On Column A Among Multiple Sheets

Nov 24, 2013

I have a workbook with multiple sheets. I want to use VBA macro to delete the row where the first column is strikethrough. I try to the follow VBA code but it doesn't work.

Sub Macro1()
Dim cell As Range
Dim delRange As Range

[Code]....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved