Row Deletion Per Criteria

Jul 17, 2008

I have an Excel sheet that is similar to the following:

Customer----- contract ----- value
XYZ ------- 123 ------------- $50
ABC ------- 987 ------------- $150
XYZ ------- 123 ------------- $-50
XYZ ------- 123 ------------- $200

In the above example, I want to delete Row 1 and 3... it is an accounting issue, and when the values of the same contract match (50 and -50) then it needs to be deleted from the Excel sheet (whole row).

I want to leave rows 2 and 4.

View 9 Replies


ADVERTISEMENT

Multiple Criteria Row Deletion Code

Aug 14, 2007

modify the delete row macro below. Unfortantely this is where my brain cell sometimes dies and the easy answers are as apparent as they should be...

Ideally what I am looking to do is have a growing list of "IDS" in one worksheet (call it delete IDS) that will be used to loop thru the sheet named "NRs" and delete the the IDS from that worksheet.

Sub DeleteNames()
'using set column
Sheets("NRs").Activate
Dim RngCol As Range
Dim i As Range
Set RngCol = Range("A:A")
For Each i In RngCol
If i.Value = 156916233 Or i.Value = 600093 _
Or i.Value = 156970571 _
Or i.Value = 156948409 Or i.Value = 30560 _
Or i.Value = 163841503 Then _
i.EntireRow.Delete
Next i
End Sub

View 4 Replies View Related

Prevent Deletion Of Row

Jun 11, 2014

I am trying to write a VBA code that prevents the user from deleting row 8.

I do not want to use the sheet protection with password approach.

I simply want a code that prevents the user from deleting row 8 and notifies him via MSGBOX that such action is prohibited.

View 1 Replies View Related

Disable Row Deletion

Mar 13, 2009

Excel 2003 - VBA - How do you detect when a row has been deleted in VBA? Better yet, how do you detect when a row is about to be deleted? I also would like to know the row number. I do not see an event handler that will inform me of the "Delete" event.

View 2 Replies View Related

Row Deletion - Advanced

Jun 29, 2006

I am working on a large Audit project for one of my many bosses and I am a little stumped so I came to the experts. I have a list that is organized randomly and we need to have 95% of the cells deleted. I don't want to have to go through each time and calculate that out each time.

I am sure that I would have to find the last cell used in one of the columns and then multiply that by 95% and then delete from that row down in the sheet. (Since the sheet is already organized randomly it can delete the bottom 95% of rows.) I am not sure how to put it all together.

View 9 Replies View Related

Conditional Row Deletion

Dec 27, 2006

I have read several posts on this subject as well as the two common methods recommended by Dave. Hovever, I have not been able to incorporate the other thread recommendations to my specific problem. I want to delete entire rows based on the absence of a matching criteria from a list on another tab. Example: Tab1 contains a list of names in A1:A15. Tab2 contains rows of data wherein column C lists names that may or may not be in the list on Tab1. I want to delete rows that have a name in column C that does not match any of the names in the list on Tab1 A1:A15. Here is the code I have pieced together thus far:

Sub Delete_elimntd_players()
Application. ScreenUpdating = False
Sheets("QB").Select
Range("C2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value <> 'NEED CODE HERE' Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Application.ScreenUpdating = True
End Sub

View 2 Replies View Related

Deletion Of Row Conditional?

Jun 9, 2007

I have been pulling my hair out for hours now trying to figure this out. I want is to figure out is how to compare an address in Column C to an Address in Column H, If they don't match I want excel to delete the row. I have 7500 rows to compare. I have read lots of posts and looked at the code for delete row conditional but I don't seem to be able to put it all together.

View 2 Replies View Related

Prevent Sheet Deletion

Sep 3, 2005

Is it possible to prevent a particular sheet from being deleted?

I have a workbook that contains sheets (of course). One of the sheets
serves as a "Help" sheet. I don't want users to accidentally (or intentionally)
delete just this sheet.

View 10 Replies View Related

Protecting A Sheet From Deletion

Mar 26, 2007

I have a workbook with particular worksheets that should never be deleted. If they are deleted by the user the workbook will become unusable.

The users need the freedom to edit data within the sheets (so protecting them is not an option), however, I need to restrict this freedom so that they cannot under any circumstance delete the sheet. Is there such code that I could use in the worksheet code to stop this?

View 12 Replies View Related

TextBox Deletion Of Text

Aug 24, 2009

I have a SS in 2007 with 7 text boxes on it. When I go into developer and select them, they all have the same name in the name box of TextBox 68.

I would like to be able to clear all the text in the boxes and have yet to come up with any code to tackle this. Why do they all have the same name?

View 8 Replies View Related

Inputting Parameters For Deletion Of Row In VBA?

Aug 28, 2013

I have some code that works fine below. It deletes an entire row based on the value in column J.

Ideally I would like to give the user the option to choose which column to base the deletion on. i.e. some kind of drop down box or input parameter where they could choose a different column "A", "B", "C" etc...

Private Sub CommandButton1_Click()
'Removes values less than 0
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("J1:J1000"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value)

View 1 Replies View Related

Protecting Formula Deletion

Jul 10, 2008

I have a shared spreadsheet which contains only very simple "sum" formula's and I am trying to stop users accidently deleting them when they are inputting numbers.

I have tried using data validation, but that only stopped people overwriting the formula and not deleting them.

I also tried protecting the sheet, leaving only a cell range which users were allowed to edit, which worked... BUT I also have several rows which are grouped and when I protect the sheet, I cannot collapse and/or expand these groups.

View 9 Replies View Related

Speed Up Row Deletion Array

Nov 20, 2008

I currently have a macro set up to delete rows if a certain user selected value is not found in a certain column. It works fine if the sheet isn't overly large but the problem is we have some spreadsheets with 25,000+ rows and it takes time to loop through - I'm not sure if there is even a way to make it faster.

Currently it works by looping backwards on the sheet checking each value in the cell versus an array containing the user selected values. If a match isnt found it deletes this row.

'y = long value representing row
'x = counter for each item in user selected array
'wsSheet = worksheet we are using
'rowLast = last row on spreadsheet
'arrSearch() = string containing user selected items

i have screenupdating/calculations/events turned off - i just didnt know if there was a faster way of looping through

i just thought about instead of doing an array maybe joining the array into 1 string and using a "like" comparison check to see if a match is found - would save me from having to constantly loop x * y amount of times - not sure if this would work or not. ill post back if it doesn't

View 9 Replies View Related

String Deletion Not Working

Dec 30, 2009

I am currently using this code which works:

If Me.txtNSNumber.Value = "1" Then
Me.Height = 198
End If

This is the original code I found and believe it will give me what I am trying to achieve (I have tested and works):

Dim ctl As Control

For Each ctl In Me.Controls
If Left(ctl.Name, 3) ="txt" Then
ctl.Text =""
End If
Next ctl

I have tried to modify to suite my requirements however it fails to work correctly (no error messages just does nothing). Basically I am trying to combine them so that the various textboxes named as txtNS1 (with varying characters after the numerical digit) would clear the data dependant on the value of txtNSNumber.

This is my attempt at modifying (from my basic knowledge) which I have tried to altered the string start and character elements without success:

Dim ctl As Control
For Each ctl In Me.Controls

If Me.txtNSNumber.Value = "1" Then
If Mid(ctl.Name, 6, 0) > "1" Then
ctl.Text = ""
End If
Me.Height = 198
End If
Next ctl

View 9 Replies View Related

Prevent Some Worksheets From Deletion

Jan 5, 2007

i have a workbook with 6 worksheets in it. the user can put data into any cell in any worksheet. what i want to be able to do is prevent the user from accidently deleting any of the worksheets

View 8 Replies View Related

Preventing Cells From Deletion

Apr 11, 2007

How can I protect individual cells in a range from being deleted when I want to delete data from the surrounding cells??

View 3 Replies View Related

Prevent Deletion & Allow Entry

May 2, 2007

I am trying to complete a spreadsheet that will be shared by several users but have come across a slight problem. I need them to be able to enter text into blank cells but when the the text has been enetered for them not to be able to delete or edit it again in columns A & B. They should also not be able to delete rows or columns. I have tried with other peoples help but as soon as the spreadsheet becomes a shared document I get a run-time error 1004 when entering in any box

View 9 Replies View Related

Preventing Accidental Deletion

May 8, 2007

I am trying to make a userform in my excel document that will allow the user to select (perhaps from a combo box) and delete a sheet they want. More sheets will be added and so the combo box or whatever should be able to reflect this. It should prevent them from deleting sheets I specify in the code as well. I've had a quick go doing this myself but I decided to start again as I had soo many problems. I think I just did it in a very long-winded fashion, so I would be interested to see what people come up with.

View 2 Replies View Related

Protect Cells From Deletion

May 22, 2007

I would like to protect some cells of a spreadsheet, without using Excel's protection, because I only want to prevent the deletion of those cells - the cells must remain editable.

View 4 Replies View Related

Show Warning For Row Deletion

Dec 11, 2007

I need to test whether one or more rows are about to be deleted in a worksheet.

Users delete rows to edit a worksheet, which affects other columns. I need an opportunity to warn them before the delete occurs.

View 9 Replies View Related

Re-Number Data On Deletion

Jan 24, 2008

What I have is a list of members in excel 2007. The first column has a member number. Starting with 1 and ending with the last member. Sometimes I must delete a member and this causes a missing number. What I would like is a simple command in excel that I can not find that would change all these numbers so there is no longer the missing number. I.E. 1 through the end of the list. A re-number command.

View 5 Replies View Related

Deletion Of Words From List In Excel?

Feb 6, 2013

how could i delete numerous words from various entries in excel data list

e.g. salman (Ali) Khan - i want to delete (Ali) from the cell value

View 6 Replies View Related

Protect Specific Sheets From Deletion

Dec 19, 2007

Is there anyway to prevent certain sheets from be deleted?

i know there is protect workbook, but that prevents the user from deleting any sheet

View 12 Replies View Related

Macro To Recognise No. Of Rows After Deletion

Dec 25, 2007

In my attachment, I had try to create a PO system that will show a dialouge box and capture input data of the dialogue box into the worksheet.

However, I am not able to make the macro recognise any manual deletion from the worksheet (e.g. delete any row in between the full data). Hence, when I input new data, the row will start from the last register counting of rows (i.e. Range("g1") in my attachment.) and it will leave a blank row after the last row of data and so on...

I will also like to know if I need to fix the "date" input to select from a "calendar" pop-up and input the date format into (e.g. year into column a, month into column b and day into column c) 3 separate columns. Is it possible?

View 10 Replies View Related

Macro Not Responding To Cell Deletion

Nov 12, 2008

if column E is "Annuity", you can type a value in column J and it runs a formula to calculate a value for column K. If E is not "Annuity" it clears the contents of J (which should clear K). What's not working is when column J gets cleared or manually deleted, column K must be blank. The worksheet_change event is not working for deletion of column J.

View 5 Replies View Related

Multi-Conditional Row Deletion With Dates

Apr 3, 2009

Here are the examples (pictures):

Example 1 Before
Example 2 Before

You can see that it's an history of currency trades (Forex). What I want is to see how much money one would have made letting only one open position per currency pair. A basic algorithm would be something like this:

View 5 Replies View Related

Preventing Cell Reference Deletion

Nov 10, 2008

I have a workbook that contains 2 worksheets. The first worksheet contains all the data. The second sheet contains formulas that reference (absolute for all) the first sheet.

I run into problems (actually the users do) when the data set is updated with new information. Sometimes the users will delete all the data rows first and then copy the new data to the blank cells. When they do this, it messes up the formulas with #REF! error. Is there a way to prevent this? or is there a way to hard code the formulas so I don't get this error?

View 4 Replies View Related

VBA Code For Automatic Deletion Of Rows

Apr 1, 2009

My data is in the following format,

Company Name Bill Status
GTAEPL ACCEPTED
GACEPL REJECTED

I need a vba code if the in Bill status column rejected is there all those rows to be deleted.

View 9 Replies View Related

Stop Cell Deletion Without Protection

Nov 28, 2006

I have a cell with data validation: list, that allows values contained in named range. I want to disallow to delete contents of this cell (with 'del' key, or when i edit cell, delete text and then press 'Enter'). Is it possible to do with formula or VBA

View 4 Replies View Related

Deletion Of Empty Characters From A Cell

Feb 25, 2007

I have a cell for example with:
"

11111
22222

44444

77777


"
What the effective way to delete all empty rows in current cell? I confused with VbCrLf, VbNewLine and e.t.c I want to get after that:

"
11111
22222
44444
77777
"

View 4 Replies View Related







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