Compare Dates In Rows - Delete Then Shift Up
Mar 14, 2012
I am fitting 2 sets of data to match eachother. I want to have as much data as possible but only for the same dates of each dataset.
I have 4 coloumns.
Dataset 1: A is date B is data
Dataset 2: C is date D is data
I want to compare A vs B one row at a time. If they are not similar it should delete and shift up the values in A+B or C+D according to the lowest value (date) of A vs B.
Remember that if the macro just deleted and shifted up it should compare the same (undeleted) value against the new value that just shifted up in the other coloumn.
View 2 Replies
ADVERTISEMENT
Nov 24, 2007
I have a column of dates (B2:B1500) that I want to compare to a single date (A1).
If the dates in Column B are older than the date in A1 I want to delete the entire row.
View 3 Replies
View Related
Feb 4, 2007
VB code to carry out the following task?
Look at name in cell BC2, check the name in cell BA2 and if is the same delete the name in BA2 and BC2.
If is different then leave both cells as they are and move onto checking BC3 against BA3
carry this loop thru BC2 : BC40
View 9 Replies
View Related
Feb 22, 2008
I have a large data file. In column A is 8760 entries (every hour of every day of the year). Columns D & E are linked, Column D is the time and date whilst Column E is a parameter assocaied with that time and date.
There should also be 8760 entries in these columns, however there are some missing data lines in columns D & E. I would therefore like to move the rows in columns D & E so they match with the entries in column A. Can anyone help? I have a headache as i have over 70 files to complete this operation on.
View 9 Replies
View Related
Feb 5, 2009
I've got two workbooks, Workbook1 with a list in column a and Workbook2 with a list in column F. I want to compare the cells in these columns and delete the entire row in Workbook2 if there is a match.
View 3 Replies
View Related
Mar 31, 2014
I have a list with start dates. These go back about 2 years and forward about 2 years.
What Im after is deleting anything older than say 30 days and anything in future more than say 120 days.
I have found various codes with varying success. This one works a charm....
[Code] .....
the problem is, I dont want the text boxes and would rather I had something like a =today() - 30 OR = today() + 120.
View 5 Replies
View Related
Apr 1, 2009
I need help deleting rows which contain a cell with date / time in the format dd/mm/yyyy hh:mm if the value is older than the current date / time
any ideas?
View 6 Replies
View Related
Jan 22, 2008
I have the following code to compare two columns and delete adjacent rows if 1 is greater than or equal the other...
Sub LastReceipt_GT_Confirmed()
Dim intLstRow As Integer
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1
With Range("E" & intLstRow)
If .Value > .Offset(0, 1).Value Then .EntireRow.Delete
End With
Next intLstRow
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 .............
View 9 Replies
View Related
Jan 3, 2010
The number of rows you are pasting and then you can select that same number of existing rows and use "insert". This is what I'm trying to achieve:
(before paste)
A
B
C
(after pasting unknown number of rows)
1
2
...
n
A
B
C
Is there really no built-in way to do this?
View 6 Replies
View Related
Nov 7, 2009
I have data in cells A1:FM1326 I want to delete all of the cells that say FALSE and shift the remaining cells up.
View 5 Replies
View Related
May 15, 2014
How would write a macro that would delete a row and shift up if they have a value in Column A?
View 6 Replies
View Related
Feb 6, 2007
I have a problem in making a delete function/sub and i'm applying it to a listbox in userform ... i'm trying to delete a row of data in the listbox which refers to cells (A12:D12) ... and at the same row (row=12), there are other data cells (E12:H12) ... after deleting the cells all other data below the deleted cells will shift up taking over the deleted cells ...
View 9 Replies
View Related
May 28, 2014
I want vba code to do the changes
Search for "CAP_ACTV_LN_SEQ" keyword in column A of Sheet1 and when found go two rows down and do the following
Delete cell D128 and E128 ( if more than 1 rows then do the same for all rows till "selected" keyword detected below it eg C131 in this case ) and Shift cells in the right to two column left as shown in figure below -
Before
2.jpg
After
3.jpg
Sheet attached
Data.xlsm
This can happen by click of button in the sheet
View 3 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
Jul 1, 2007
I am looking for a macro that can delete whole rows (shift cells up then). How i would like it to perform is if there is nothing in cell(s) B then delete the whole row?
View 9 Replies
View Related
Mar 15, 2007
I'm trying to do is check each Row and move the last 3 Columns of data in each Row to the left so they fall under the headings NHA2, NHA1, and OEM PN (Columns U, V, and W). Columns A and B are temporary. Column A utilizes the formula "= COUNTA(B2:AG2)" to count the number of cells in the row that contain data. Column B utilizes the formula "=COUNTA(C:C)" to count the total rows of data in the active worksheet. Columns A and B will be deleted at the end of the Macro.
The GOAL (END RESULT) is for Columns A through W (and ONLY these Columns) to contain data (keeping in mind that Columns A and B will be deleted at the end). If some rows have Column W (and beyond) blank, then I want to MOVE data from Columns U & V over to V & W and then COPY data from Column T into the [currently] blank Column U.
I've been trying to get the Macro to start in the last row and, using CASE Statements, delete the proper range of cells and SHIFT LEFT as it counts backwards towards the first row. I'm not too sure this is the best approach and could really use some advice from the experts! I can provide a "test" file if necessary ... the test file I've been working with is approximately 6.5MB, but I can delete most of the 14,287 rows and still give a good representation of how the data varies.
Sub b_DeleteCellsShiftLeft()
' DeleteShiftLeft Macro
' Macro recorded 3/14/2007 by George Nicholaou
' Need to assign variable for current row (?)
' r=ROW()
' Columns A and B are temporary
' Column A utilizes the formula "=COUNTA(B2:AG2)" to count the number of cells in
' the row that contain data
' Column B utilizes the formula "=COUNTA(C:C)" to count the total rows of data
' in the active worksheet
' They will be deleted at the end of the Macro
' What I want the Macro to do from this point is:
' 1. Check each Row and move the last 3 Columns of data in each Row to the left so
' they fall under the headings NHA2, NHA1, and OEM PN (Columns U, V, and W)
' 2. The GOAL (END/RESULT) is for Columns A through W (and ONLY these Columns)
' to contain data (keep in mind, Columns A and B will be deleted at the end)
' 3. If some rows have Column W blank, then MOVE data from Columns U & V over to V & W............
View 9 Replies
View Related
Aug 8, 2013
I have a sheet,i want the users unable to select any cells,but a column is free to select an change and enter the inputs. I want to delete and shift up zero cells on that free column,but this property is disable when i protect the sheet.
How I protect other cells from selection and change,and enable delete and shift up for the column that not protected.
For example: I have the attachment file,every column is protected except column "H". I want a macro to when I clear content of any cell in this column,every cells shift up and every non zero cell put from row 1 to... and after them zero cells put.
View 2 Replies
View Related
Oct 7, 2008
I am trying to find a method for deleting empty cells in a range and shift cells left upon deletion automatically. I could do this manually, but with 3000 rows of data it could be a long task. I have attched a small spreadsheet to show what I am looking for.
View 5 Replies
View Related
Sep 7, 2012
I am trying to create a macro that will add and delete individual cells depending on an IF statement. The code that I created deletes/adds cells regardless of the IF statement.
If Sheet1.Range("F3").Value = Sheet1.Range("G2").Value Then
Range("G2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End
ElseIf Sheet1.Range("F3").Value = Sheet1.Range("G4").Value Then
Range("G3").Delete Shift:=xlUp
End
End If
Also, what is the easiest way to have this code apply to all rows within column F, while adjusting the corresponding cells referenced in column G? The last row within the spreadsheet changes.
View 3 Replies
View Related
Oct 7, 2008
I am looking for a method which would delete empty cells in a row, and then shift the remaining data to the left (filling the cells with data). I know that this could be done manually, but I need it completed for 3000 rows! I have attached a simple sheet to show what I'm Looking for.
View 2 Replies
View Related
May 23, 2007
I have a spread sheet that has RX codes as field headers and ones or zeros as values. I want to find all cells which contain "0" and delete those cells while shifting the remaining cells left. The range of cells is aj1:dr17365 and the sheet name is "May 07 Rx HCCs"
View 8 Replies
View Related
Dec 1, 2009
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
View 3 Replies
View Related
Apr 11, 2008
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
View 8 Replies
View Related
Oct 12, 2011
how to write a macro where I can select a row or rows (adjacent) press a button, and the secelected row(s) will shift down one row every time the button is pressed. I also need another button with a macro that shifts the row(s) up one row at a time. The following code works for shifting selected row(s) up, but I can't figure out how to shift multiple rows down a row at a time.
Code:
Sub RowUp()
Selection.EntireRow.Cut
Selection.Offset(-1, 0).Insert Shift:=xlDown
Selection.Offset(-1, 0).Select
End Sub
View 8 Replies
View Related
Mar 1, 2007
I am running excel 2007 and I have 23 worksheets in a workbook. I have no links or hidden rows or colums. I have no merged cells or comments. My biggest sheet is A1:
AW8096. I use VB macros to add lines and move data from one set of workbooks to this one.
I have built several sheets using this proceedure.
All of a sudden I get the error "Cannot shift objects off sheet" when I run
a routine I have 50,000 times. This even happens on new blank sheet so I know its' not on the sheet.
I have checked to make sure there are no hidden comments, row or columns. I have deleted most of the worksheets and I still get the error. I have made copies of the workbook and used different file types to save it. I can create a new black sheet in the workbook that is empty and the same thing happens.
The only way I can get it to work is to creat a new blank workbook and copy and paste the sheets into the new workbook. Yes I have serched the FAQ and knowledge base and tried all of those fixes as well.
View 9 Replies
View Related
Oct 22, 2009
The below code is what I have and it works but what I need is for it to loop until it doesn't delete anything. How can this be done?
View 2 Replies
View Related
Jul 16, 2007
Using Excel 2003 on XP.
In Workbook1 I have a list of users with a leave start date in column N and a leave end date in column O.
In Workbook2 some of these of users appear with a payment start date in column C and a payment end date in column D.
I need to find out if the any or all of the payment period for these users in Workbook2 falls within the date ranges of their leave in Workbook1. If not, I need to ignore (or delete) the payment record in Workbook2 for that user.
Is this possible using VBA? Mind you, I'm stuck either way as I simply have no idea about how to approach this (possibly ... would first need to work out how to identify if the dates cross and then perform a lookup ...?)
View 11 Replies
View Related
Jan 29, 2010
I have two columns (G&H), one has expected date, and one has actual delivery date. The line amounts vary from 2500 to 8000, I need to calculate if column H is greater than column G for each line, if it is not then that line can be deleted.
View 4 Replies
View Related
Oct 30, 2009
I have the following date formatted strings in two Excel cells "17/12/2009" & "19/12/2013".
Now I try to run the following code on these cells:
If Range("B" & i).Value < Range("B" & j).Value Then
But it does not seem to work, as the dates are compared incorrectly.
Any clue how to bring these dates in a VBA readable format?
View 9 Replies
View Related
Jul 17, 2009
I am trying to compare the difference between to dates (a person dob and the date of an activity they have carried out and then output the age of the person when this activity was done:
for example:
activity date: 08/01/2008
dob: 05/12/2007
View 14 Replies
View Related
