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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
