I have a series of ranges (over 26000) on a s/s and the top five rows in column I consist of:
1. number
2. text + number
3. formula
4. number
5. formula
I wish to delete the fifth entry in all ranges.
Ranges vary in row length from 5 to 30 deep.
I am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.
I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.
hello. i'm new to excel and i'm really hoping for some serious help here. i have the basics down however i'm really stumped at this point. Let me try to explain my worksheet. the worksheet has a sheet for data which is used for drop list values in the new patient template sheet. the new patient template which is just that a blank entry sheet that the user duplicates and adds a new patient to the worksheet to track the visits made by medical staff. there could be over a hundred new patient sheets (each named by the patient) at any given time.
here is what i need help with: my sheet is setup on a monthly basis so each patient has a total of 4 - 5 weeks listed with entries for everytime a nurse visits that patient. what i need to know is how to search the cell entries for the first time a visit occurred and the date it occurred and this needs to be broken down by the 1st of the month - the 15th and then again for the 16th - the end of month for every patient sheet in the workbook. the ranges are not together that i need to search for example f14:f20, i14:i20, f35:f41 and so on for the first 15 days of the month. this all needs to happen like in a macro or somehow automatically.
I am in need of a macro that will allow users to delete an entry in a named range as well as delete a range named the same. I would like the macro to open a list showing the contents of a range named "Locations". Once opened the users may select a name from the list and click on a button to remove that item from the named range. the macro can then delete the cell that name is in and shift the remaining cells up, changing the range. Second the macro should delete a named range that matches the one the user selects. once delete I would like to have a pop say "Location Removed Sucessfully".
View 9 Replies View RelatedI am trying to create a chart that will Add or Remove data entries as toggle buttons are clicked. I've fallen at the first hurdle. When I remove one series from the source data, all the SeriesCollection numbers will change down. e.g. ->
ActiveChart.SeriesCollection(2).Delete
where (2) will change to (1) when I delete a series. This then screws up subsequent Removal macros. Is there any way I can either lock the SeriesCollection number?
I'm trying to get one more feature added to this priority queue tool that a forum member created. Essentially, this attached spreadsheet is a dynamic task/priority list that will change on the fly. See the original thread here: [URL]
The only missing feature is the ability to delete an entry and have all other entries below it change up a rank.
The attached spreadsheet is the almost completed solution
Example:
Original
-----------
Item D - 1
Item B - 2
Item E - 3
Item A - 4
Item C - 5
Remove item B
-----------
Item D - 1
Item E - 2
Item A - 3
Item C - 4
I have data input in a particular column, say Column C.
I would like to have a macro that deletes the rows where the corresponding entry in Column C are blank.
In addition, there are multiple worksheets with the same data format in the same spreadsheet, but the number of row varies. It would be great if the macro can delete the row with blank cell in that column across all worksheet.
When I use a reference to populate a cell in anothe worksheet and I use the delete key to remove the entry, a zero shows up in the cell of the worksheet wit the cell reference. For example if I use =AM!A3 and use the delete key to delete the entry in cell A3 of worksheet AM, the worksheet with the reference =AM!A3 shows a zero. Is there some way to prevent the zero from appearing in the cell with the reference?
View 3 Replies View RelatedIs there a macro to delete an entire row if a duplicate entry appears only in a certain column.
1. Look for the column header with the name "File Number"
2. Anytime the same number under the "File Number" column appears more than once in that column, keep the row that contains first occurrence of that number buy delete the entire row anytime that number is repeated in another row in that same column.
This is regardless of what is contained in the other columns. For example..let's say these cells contained this data...
B1 - UTE00225
B2 - UTE00546
B3 - UTE65513
B4 - UTE00225
B5 - UTE00225
In this case, I would want to keep rows 1, 2, and 3. But, I would want to delete rows 4 & 5 because the number "UTE00225" has already appeared first in B1. I'm using Excel 2003.
I have a number of crazy defined names in my spreadsheet (1,746 to be exact). Not sure where they all came from, but I need them gone gone gone. Some of them start with squares at the front of the name and most of them refer to =#ref! or similar. I've seen a number of vba solutions to this, but none of them seem to work for me - I get "400" errors or run time errors. For reference, I'm hitting Alt F11, pasting the whole sub in, saving, then running.
View 9 Replies View RelatedI have thousands of questions & answers arranged in two consecutive columns as shown below (here for your convenience,: is the column separator). I want to delete all the duplicate Question and Answer sets keeping only one set. Sometimes questions may be same but answers differ as shown below. In this case I want to delete the colored/highlighted range.
Question: xyz
Correct Answer: abc
Question: def
Correct Answer: pqr
Question: xyz
Correct Answer: abc
Question: xyz
Correct Answer: asd
I have a userform that allows a user to enter data from columns A to P. I successfully installed a macro (which I got from this site:[url]
that deny a duplicate entry in Column A. The macro then "clears" the duplicate value in column A, which is okey. However, I need help for a code so that the
rest of the entries from Column B to P (within the same row) will also be cleared automatically. I tweaked the "target.value" quite unsuccessfully.
I am attempting to delete external named ranges after a sheet is copied out of a source workbook (the template). I have tried both the following codes, but neither one does the trick. The code is in ThisWorkbook (not a module), and I have tried it with the code both in the template and the new doc. The new doc is where I want the names to be deleted (there are no #REF names in the template).
Deletes named ranges based on the reference to the source doc:
Code:
Sub DelRanges()
Dim nName As Name
For Each nName In Names
If InStr(1, nName.RefersTo, "='T:Fin_") > 0 Then
nName.Delete
End If
Next nName
End Sub
Deletes named ranges based on a #REF in the value of the range (this is my preferred method):
Code:
Sub DeadRanges()
Dim nName As Name
For Each nName In Names
If InStr(1, nName.Value, "#REF") Then
nName.Delete
End If
Next nName
End Sub
Doesn't happen all the time, and generally happens when a macro is run.
View 9 Replies View RelatedBasically I have the code to Delete All named ranges in active workbook, but I need it to skip over two named ranges called Categories and Length. Is there a way to adjust this to delete all named ranges in active workbook except a named range Categories and another called Length
Dim rName As Name
For Each rName In ActiveWorkbook.Names
rName.Delete
Next rName
I'm trying to delete all the Named Ranges in my excel document, but it appears there are some hidden ranges, and I can't delete them! How do you delete a range that is hidden? If I try to delete it with my code it says "That name is not valid." So I created a message box to see what the name of the range is.....and it's like 30 letters long! It starts like this..."={#N/A,#N/A, TRUE "Summary....." I woud like to attach my Excel file but it's too large. Below is my code
Sub Remove_Hidden_Names()
' Dimension variables.
Dim xName As Variant
' Loop once for each name in the workbook.
For Each xName In ActiveWorkbook.Names
If xName.Visible = True Then
xName.Delete
Else
MsgBox (xName)
xName.Delete
End If
Next xName
End Sub
I have a report that contains several rows of data. This data is separated by page breaks, so for example, there could be 1500 rows, separated into 90 pages by page breaks. Each range may have anywhere between 35 and 45 rows, but some of those rows may contain no data.
I would like to loop through the cells column A, look for a certain criteria, then delete the range around that certain cell that is contained within the page breaks.
I have code that will loop through the cells in column A. I also managed to create code that will delete a set range but since the ranges aren't always a uniform number of rows I need another fix.
I was after a bit of code to delete the range names on a particular worksheet I thought it couldn't be that hard but have only been able to find this in the archives, unfortunately I get an error when trying to execute it:
Sub Delete_My_Named_Ranges()
Dim n As Name
Dim Sht As String
' Put in name of sheet where the range is located
Sht = "Org Lookups"
For Each n In ThisWorkbook.Names
If n.RefersToRange.Worksheet.Name = Sht Then
n.Delete
End If
Next n
The error I'm getting is Run-Time error 1004, Application defined or object defined error.
I have a dynamic named range in my worksheet:
Range Name = AssignDt
RefersTo: =OFFSET('Raw Data'!$W$2,0,0, COUNTA('Raw Data'!$A:$A)-1,1)
I perform a routine in VBA which deletes unwanted rows of data from my worksheet. The problem occurs if row 2 happens to be one of those rows. It not only deletes Row 2, but it also deletes my Named Range.
I have a spreadsheet, but it came from another file using the detach sheet method. It has therefore taken with it, all the named ranges that are now superfluous.
It seems a bit long-winded to delete these named ranges one by one manually. Would there be an easy to understand script that would delete them all, so I can start with a "clean sheet".
I'm using Excel 2003.
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
I have a spreadsheet on sheet 1 with a list of customers and their information. So on column A I have the customer number (i.e. k968, e37, p528,...), on column B i have the customer's name, on column C the street's name, on Column D the house number, on column E the zip code and finally the city on column F.
Right now there are around 600 customers in this list.
I have made a userform with a combobox in which I want to select an existing customer (pulled from the spreadsheet). On the same userform I have textboxes (customer number, name, street, number, zip, city). When I select a customer in the combobox, I want this customer's info to show up in the textboxes. I want to be able to change the info and hit Next to store the changes in the spreadsheet. When I do not select a customer from the combobox, I want to add new info in the textboxes and hit Next to store this info as a new customer. The userform also has a delete button. Then I select a customer in the combobox, this customer (and it's info) should be deleted from the spreadsheet when i hit Delete. So the spreadsheet is variable in length.
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 Replies View RelatedI used a form with textboxes for data input for one of my vb macros. Currently I have the private sub from the form transfer those entries to a remote cell on the spreadsheet, (like in column "HZ"), so that the macro that will actually utilize them can retrieve them. Is there a way to pass that data directly from what is entered in the form in the textboxes to the macro that will actually use them?
View 4 Replies View RelatedI am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
say sheet 1 has 2 collums A & B
collum A is Names Collum B is Dates
A B
Bob Fenton 05/04/08
Rob Smith 05/06/08
Al Feth 05/08/08
Al Feth 05/18/08
Al Thomas 04/23/08
Rob Smith 05/23/08
Bob Smith 04/22/08
Bob Fenton 05/15/08
Al Feth 05/10/08
sheet 2 has unlimited collums in collum A is the name of the person in collum B to Z (or more) i would like a fomula that will search sheet 1 and return the dates for each entry of that name.
so sheet 2 would be like ....
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies View Relatedi have an address that is all in one cell displayed like
101 hampton Court, Hampton heath, Hampton Town, Hamptonshire, HA01 1AS
but i need to have it split in to individual cells so
Cell A1 would be 101 hampton court
B2 Hampton Heath
C2 Hampton Town
D3 Hamptonshire
E5 HA01 1AS
each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle.
also i need it to work backwards ie
it finds the post code first,
then the county
then the town
as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
definitions for static ranges and dynamic ranges?
View 9 Replies View RelatedI have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
"
A B C D
| SUMMARY
model qty| modelqty
1 4.12922.0000| 4.12952.2000
2 2.000012.1250| 2.000025.1250
3 4.12929.0000| 318.0000
4 318.0000|
5 4.1291.2000|
6 213.0000|
"
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model