Delete Cells If No Numbers In Same Range
Apr 29, 2008
For the description below I will be referring to the file I have attached.
I would like to write a VBA macro which will look at columns 1-4, if ALL of these are blank it will take the results from the row below in their place.
So the code column on the left will be displayed next to columns 1-4 in the row below, if columns 1-4 adjacent to the code are blank.
The 'Raw Data' columns show how the raw data will be fed into the spreadsheet.
The 'Output' columns show the desired output I would like from the raw data columns.
View 9 Replies
ADVERTISEMENT
Sep 23, 2013
I have 50,000 to 150,000 rows of data. Row M has data from 0001 to 9999. I need to delete all rows except the rows with values 2500 to 4999. I had this working with a similar and smaller spreadsheet awhile back using an advanced filter. In this case I need to delete the unneeded rows. I would imagine I could also achieve it with a VLOOKUP array and a lot of finagling but with this many records to repetitively
View 8 Replies
View Related
Feb 27, 2014
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
View 7 Replies
View Related
Mar 5, 2012
I have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
View 4 Replies
View Related
Jun 23, 2008
I need to remove multiple instances of pages numbers from a 2000 character text string I have dumped into a cell. The spacing is not equal between the page numbers but they always take the form |#####-##| i.e. bar delimited 5 digit-2 digit. I tried SUBSTITUTE but it will not accept wildcards. I was thinking of looping through a SEARCH. The numbers are ordered ie |00001-01| to |00001-25| then |00002-01| etc, stopping at 25. This might lend to looping but I couldn't wrap my head around the VBA to accomplish that. A sample of the text
"|00006-01| (Defendants' Exhibit Nos. 1,2 marked for |00006-02| Identification.) |00006-03| BY MR. JOHNSON: |00006-04| Q. Doctor, I am handing you Exhibits 1 and 2. |00006-05| Exhibit 2, is that the one that you brought with you, |00006-06| the deposition notice of today? |00006-07| A. I believe so, yes." Auto Merged Post Until 24 Hrs Passes;I should have said this before but this is for use on Excel 2003 WinXP.
View 8 Replies
View Related
Jun 25, 2008
i m creating a macro to delete extra characters in a column. I have over 200,000.00 records in one column which consists of numbers with characters. For example:
#80723666
-80726960
80730187--
/ 80730279
80736277 /
( )80739210
* 80739823
& 80735380
80796440 @
## 80722138
if anybody can come up with a macro, so that I can just have the numbers and nothing else. I would like to exclude the following -~!@#$%^&*()_=+?/.";:|][{},^` within a cell.
View 4 Replies
View Related
Apr 21, 2014
I am looking for a formula that either accepts the number in a cell if the numbers on two other cells are "approved". In other words, I have on cell A1 my value to be tested. On cells A2 and A3 I have two numbers. On cell A4 I'm looking for a formula that copies the number on A1 if A2 is bigger then 0.25 and A3 is smaller then 0.35. In not, there's no copy or a N/A appears.
View 3 Replies
View Related
Feb 15, 2014
I have the below given macro which does quite well at deleting the cell for a given value (Value = "_y-600_z-.jpg") in a given range. What I need for it to do is delete the cell contents if '_y-600_z-.jpg' is anywhere in the string. As an example, if the cell contains 'Handler_x-111251_y-600_z-.jpg' it will delete the cell contents.
[Code] ......
View 2 Replies
View Related
Mar 1, 2014
Not sure if this is better suited for a macro or done via conditional formatting.
Trying to empty a standard template we use and of a certain range I need to empty all cells not containing N/A (typed not error).
View 2 Replies
View Related
Jan 30, 2013
I am trying to delete a group of four cells and have the remaining block shift up. My code is below and crashes on the red. What is the right code to get this to work?
Code:
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = Range("G1").Value Then
Range(ActiveCell:ActiveCell.Resize(0, 3)).Delete Shift:=xlUp
Exit Do
End If
Loop
View 1 Replies
View Related
Mar 13, 2014
I am looking for some code to scan my sheet names and delete all the sheets that don't have names corresponding to the values within a cell range contained on another sheet in the workbook. For example, if any sheet name, excluding a few reserved sheets, doesn't equal one of the values contained in cells A2:A23 on sheet "XYZ" then delete it.
The best solution I could come up with using my experience was to loop through each sheet and compare the name to each cell in the named range, but if it's possible to somehow declare the named range so that each sheet only has to compare to it that would be ideal;
View 10 Replies
View Related
Jul 25, 2012
im having a little trouble writing a macro to :
check if a7:t7 is empty, if so delete a6:t7 and change cell colour of a6:t7 to 'no fill'
View 2 Replies
View Related
Apr 20, 2013
I need a code to delete all duplicates (all cells) in the range A1: F100?
Exemple:
Before
31
81
43
[Code]....
View 5 Replies
View Related
Oct 21, 2013
I have a range (D5:BU5) that I will have various dates in each cell. At the end of the row is a percentage block. It has the formula count=(D5:BU5)/70 which gives me a percentage complete. The fun begins where some dates are based off annual, semi-annual, and quarterly. What I want to do is be able to delete cell values so they are not reflected in the final percentage. I know there is a marco that deletes the entire row but that is too much I just want a target cell.
View 3 Replies
View Related
Apr 15, 2014
I wanted to ask how to delete a specific range of data in a cell range using VBA
Here is a snipit of code I currently have:
Sname1 = Sheets("Numbers").Range("FX3").Value 'FX3 = cell F starting column letter
Range(Sname1 & Range("FR7").Value).Select 'FR7 = row number
If I added the following:
Sname2 = Sheets("Numbers").Range("FX4").Value 'FX4 = cell n ending column letter
Changed the code to the following: obviously this code is incorrect below:
Range(Sname1 & Range("FR7").Value) : Range(Sname2 & Range("FR7").Value).Select
View 3 Replies
View Related
Jul 15, 2014
How do i take the three highest numbers from E4:E5 & E13:E14 and add them together in E26
View 3 Replies
View Related
Jan 3, 2007
How can I sum all the values in a range of cells where the cells contain either a number or text and a number. Please note the following example?
C17=9
D17=4
E17=8TD
F17=3TP
G17=7
I want the total of 31 but excel will ignore the cells with text.
View 9 Replies
View Related
Oct 19, 2009
I use this code to delete whats written in a Range of cells ...
View 6 Replies
View Related
Feb 23, 2010
I need a VBA to delete rows with blank cells within columns F - AZ
Columns A - E contain headers but also need to be deleted if cells in columns F -AZ are blank.
View 9 Replies
View Related
Mar 26, 2014
I have a column with rows from 41:254, the first cell contains a word, and the next is blank, and this repeats the whole way down.
a previous macro i run changes one of the cells in the range (one of the ones containing a word) to a number value, i want to write a further macro to select this cell that has now changed. when it changes it will be the only cell in the range with a number in.
View 4 Replies
View Related
Mar 19, 2008
I would like to " Validate Data In A Vertical Column To Not Allow Non Consecutive Numbers Less Than 100"
756415
10
456132
7
456123
12
The above is a valid list. Below would be an invalid list:
756415
10
13
456132
7
7
456123
12
13
This part of a larger scope, but I have a macro that will crash if the data entered is entered by way of the invalid list, it works perfectly with the valid list. I am limited to one column user's will input the data via a Barcode scanner that after it recieves input it enter's a "Hard" return. This is a warehouse pickticket program, user's scan their ticket id's (numbers greater than 100000) and then the number of lines on the ticket (usually not greater than 15)
View 2 Replies
View Related
Feb 12, 2010
Is it possible to delete a column of numbers then replace those numbers from adjacent columns and the adjacent columns are then filled with the numbers adjacent to them
View 9 Replies
View Related
Mar 14, 2008
I want to ask that I have got a workbook with different number series i want user form where i can enter its start number and end number and then it finds and delete shift cells up said series number i have entered in user form please see mentioned below example.
Series
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
and i want to delete 1 to 5 numbers delete to shift cells up.
View 9 Replies
View Related
Oct 24, 2013
This is Excel 2010 on Windows 7
Trying to figure out a VB Script but don't know where to start.
I have a sheet for each month. lets take September 2013
A Column with 200 Servers (A1:A200) on the Intranet listed as server-1/, Server-2/ Server-3 ... Server-200/
Each Server lists a location of a file for each day (31 columns per row)
I am trying to check for the existence of the files on each server (The Cells contain the URL and File Name in Range B2:AF201) starting with server-1 check the URL in B2 if it exists, go to C2, Continue with that row until the file check is False (The Page will show a 404 error) if not found delete cell url and continue. do this for all 200 rows
View 3 Replies
View Related
Jul 19, 2009
I am trying to select a range based on two variables which store the column numbers. what I have is:
View 4 Replies
View Related
Jan 8, 2014
I have a column that looks like the following and I need to add the numbers:
27 skids
31 skids
56 skids
13 skids
The unit "skids" is constant. The answer I am looking for is "127" or "127 skids"
View 3 Replies
View Related
Jun 24, 2007
How is it possible to delete the phone numbers which include 39 like (323940, 393312, 124039, and so) suppose all the phone numbers are in cell B
View 9 Replies
View Related
Aug 20, 2014
following issue:
The following table is given:
flower
20
rose
flower
21
rose
[Code] ........
Which needs to be turned into:
flower
20
22
rose
flower
31
32
blossom
tree
1
3
apple
The last column is the one that dictates when a new range of numbers start. There should be one range of numbers for Rose, One range for Blossom etc.
View 4 Replies
View Related
Oct 9, 2009
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
View 14 Replies
View Related
Sep 26, 2012
A
B
C
D
E
F
G
23445566894 Brwn pdc aft
Cat1
45687930596
$5
45687930596 Gld wdget adi
Cat2
23445566894
$6
In the example above, I would like to do a vlookup in column C that matches the digits in column A to those in column F, with the output being the corresponding value in column G. I believe it involves the left function but not sure how to really use it here.
View 9 Replies
View Related
