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.
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
This time ive created a visual basic form that asks for a row number and when you put a row number in this form it deletes what is in column A, B and C of that row and shift the rows up so that there are no gaps between the data in the rows.
I need to know what code i would have on the 'delete' button of the form.
If you want me to describe it any further i would be happy to, just ask.
I have the following function in a worksheet module:
Private Function NixEmptyColumnsAndSuperfluousRows()
Me. Range("A1:A7").EntireRow.ClearContents
Me.Range("A1:A7").EntireRow.Delete Shift:=xlUp
Me.Range("BE1").EntireColumn.ClearContents
Me.Range("BE1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("AT1").EntireColumn.ClearContents
Me.Range("AT1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("AK1").EntireColumn.ClearContents
Me.Range("AK1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("Y1").EntireColumn.ClearContents
Me.Range("Y1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("O1").EntireColumn.ClearContents
Me.Range("O1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("K1").EntireColumn.ClearContents
Me.Range("K1").EntireColumn.Delete Shift:=xlToLeft..............
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?
I want to restrict the adding the rows and columns and also deletion of rows and columns in excel template, the same template is circulated to all the program owners to capture there project metrics. With that template through macro values are extracting to consolidated report. Some times program owners are adding extra columns/rows with that we are getting errors/incorrect reports.
View 14 Replies View RelatedHere's how my project will need to work. I need to transfer text files(.txt) to the same workbook, but 1 worksheet for each text file. In here, I can choose multiple files. After that, I need to delete WHOLE rows that has a blank cell(except 1st column), starting from row 4. Then the last requirement would be to fill the 1st column, a portion of the text file's name.
First, I need to click the command button in order to choose the files I want to load. first.png
And then, if I chose two .txt files. It will also load two text files. In here it is, CLASS 2014 and CLASS 2013 second.png
What I want to happen next is for "2014" to be inputted in columns 1, rows 5 to 15. And then delete rows with blank cell, in which here is rows 8, 10, 11, 12 and 15.
I have a listbox on a userform and dependent on which of the 9 values they select, I want to delete the rows in a range that do not contain the string.
I can get the solution using a match on the listbox items but not the reverse, i.e. delete non matches, dependent upon the variety of values that can be selected.
I could potentially use a filter to drop them out, is this the best way? Always struggled trying to build multi filters.
Is there any way, in which i can make my code dynamic?? For example, in a code written in VBA, there is a reference to particular sheet, say "ABC". If i change the name from "ABC" to "ABCD" from excel, the underlying code should change automatically from "ABC" to "ABCD".
I want to make my code dynamic..
I have a macro that gets activated as soon as i select the sheet which the macro is situated in. This is not so great because if i am bringing something from the clipboard the automatic macro erases what i have copied and therefore i cannot paste anything. I can make the automatic macro run from a different sheet. Here's how:
[Code]....
I have finaly completed my project to include all the bells and whistle needed to make an ok task a bit more exciting. However, i am having a problem when the code is executing. It seems to "break" at random points in the process. I am not able to duplicate this error at my home (nor do i want to) and I can not quite figure out what is causing this as it only happens at my work computer. I have tried my coworkers computer and the code executes flawlessly like at my home.
Strangely enough, when the code breaks I am able to hit 'continue' and it continues on its way until the next hiccup. Please assist as this fluke is causing my brain to spiral.
i have one excel sheet (refer attach) i give some example 7 to 8 customers, but in sheet nos. Of customer name a to z suppose to 3000 customer list. Now i want to create automatic customer alfa- numaric code.
View 2 Replies View RelatedI have already got an anwer for this long back from this site. The code was writted by Mr. Krishnakumar
the thread is here :[url]
i need some changes to be made in this code. The existing code creates and updates the details in the sheets automatically from the master data. I just need the sum of Column I in all the sheets after the last row of Column I.
selecting all the sheets and typing the formula in I column is not possible because, the last row in Column I is different in all the sheets.
In sheet 1, the last row of Column I is Row 15, in sheet 2 Row150 is the last row.
I guess something could be done in macros.
follwing is the existing
Sub TestIt()
Dim sWS As Worksheet
Dim Sellers As Range, Seller As Range
Dim lRow As Long, fRow As Integer
Dim CopyRng As Range, ws As Worksheet
What I would like to do is do a summary sheet that does a little math. I can make it add but only one row at a time. Here is the general idea...
The fields of the summary sheet are 'PromoCode', 'AdvertisingCosts', 'Labor' for example. And I want to add the currency fields (add, as in math addition) of 'AdvertisingCosts' and 'Labor'.
The fields are on another sheet. What I am hoping can be done is that all those fields for every row can be made to work without having to hard code each one.
Also I would like to have it update with any NEW rows that arise. Like if the existing rows were 'row1' and 'row2' and then some other time 'row3' is added that 'row3' automatically gets included on the summary sheet.
The following table/ code is something which I've been trying to tailor from a previous post so I'm not taking the credit for what I think is some very good code. Unfortunately I can't find the link to it - sorry!
Right, I have a number of columns containing a various amount of data entries in each with the first row being the header. I would like to generate all possible combinations of this data in one column, the entries separated by commas, that will eventually be exported as a csv file.
The number of columns and number of rows in each column will be changed regularly ...
I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".
At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):
The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True
Next cell
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete
Next cell
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.
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 RelatedI 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.
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.
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
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 RelatedIs 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.
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?
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?
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)
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.
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
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