Two Cells Only One Time Then Delete Rom
Feb 10, 2012
how to delete rows when a combinations of 2 cells in a row will repeat.
Example:
Row 1 51456264 150
Row 2 51456264 150
Row 25 51456264 160
Row 20 72546821 10
Row 21 51456264 150
Row 15 72546821 30
Row 17 72546821 10
Row 1 and 2 have the same combination of cells now Row 2 need to be deleted.
Row 1 and 21 have the same combination of cells now Row 21 need to be deleted.
Row 20 and 17 have the same combination of cells now Row 17 need to be deleted.
The cells in de same row always are next to eachother B and C.
I need to check a file with minimum 1000 rows .
View 3 Replies
ADVERTISEMENT
Dec 6, 2006
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too.
I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have.
Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......
View 9 Replies
View Related
Apr 26, 2014
i am trying to remove some duplicates in a sequence and add new cells for missing ones
View 9 Replies
View Related
Dec 9, 2009
I cant get my macro to find a set of data, skip that row and the next row. Any rows that dont contain my data I want to delete that row and the next row.
View 2 Replies
View Related
Jun 9, 2009
i have a slight problem i have this script which i want to run on all worksheets which are numbered (i.e. 1,2,3,4 etc) and to delete the rows in the F128 range which is under 00:05:00. I just cant figure it out to get it working.
View 2 Replies
View Related
May 31, 2012
Setting up a worksheet to monitor bookings within a time range of 06:30 to 09:30 then from 14:00 to 18:00. The data is collected in the following ranges. E3:E25 M3:M25 E29:E60.
I need the data collected from 06:30 to 09:30 - deleted JUST before 14:00 Then need the data deleted again JUST before 06:30 the next day.
View 2 Replies
View Related
Jun 25, 2013
The below code enters 4 labels into a userform and a corresponding commandbutton.
The commandbutton is to act as a delete button
The .name is the same value as column "I" on the worksheet, i.e. Push the button --> Search Column I --> Delete Row
Dim txtArray() As New Class1
Public Sub DataDisplay()
Application.ScreenUpdating = False
n = Worksheets("Sheet1").Cells(200, 1)
For i = 1 To 4
Set DtLabel = Controls.Add("Forms.label.1")
[Code] ...........
View 5 Replies
View Related
Sep 19, 2013
Is there a formula I can use, where I can delete specific rows/columns each time? I have to download a large set of data each week and each time I have to spend hours deleting the same rows and columns. For example, if I have to delete Rows 1,2,3,4,5 and Columns J,I,H,G every time, how do I automate this?
View 10 Replies
View Related
Dec 28, 2009
I have a spreadsheet with date and time values of the format
"dd/mm/yyyy hh:mm" in column A followed by some other data in cells of that row.
What I'd like to do is have a macro that will delete a complete row if the time value in column A lies between two times that I can specify in the macro (the dates are irrelvant)
View 8 Replies
View Related
Dec 4, 2007
I have a workbook of which has different times in columns D and E. What im trying to do is where the time difference between the columns is LESS than 5 mins I need that row deleting and cells shifting up.
I.E lets say D14 is 05.24 and E14 is 05.50. I need that line keeping but if E14 was lets say 05.27 then I need that line deleting and cells shifting up.
I need to be able to do this right down the workbook until the last entry.
View 9 Replies
View Related
May 24, 2005
i have a macro which is executing some cleaning up and then copying information from project sheets to a summary sheet. the individual project sheets are (currently still manually) copied/moved into the big(ger) overview workbook. to support the work with the individual project sheets they contain a macro that is assigned to some (three) shapes. one part of the cleanup is stripping the macros off the individual project sheets:
' remove the traffic-light macros of the project sheets from all sheets in case of unwanted or uncontrolled "infection"
For CurrentWS = 1 To SheetCount
Worksheets(CurrentWS).Select
ActiveSheet.Shapes.SelectAll
Selection.OnAction = ""
Worksheets(CurrentWS). Range("A1").Select
Next CurrentWS
on the line "ActiveSheet.Shapes.SelectAll" i get frequently (not always... ) a run time error '7': out of memory. it happens on at least two computers. i closed all other SW. according to the windows task manager information there should be plenty of memory free.
questions:
- how to get rid of the error message? OR:
- is there another (more smart) way to strip the ("imported") macros, while keeping the "main" marco alive that does not lead to an error message?
View 2 Replies
View Related
Jul 18, 2014
I have multiple sheet in excel.All the excel sheet has a table with formula.Now i want to delete all formula in my all excel sheet at one time without delete/loosing my values.
View 4 Replies
View Related
May 23, 2014
I have a UserForm which writes data to rows in a master spreadsheet. I'm attempting to write some vba code for a CommandButton in the master spreadsheet which can identify and delete duplicate entries based on "user ID", "Date", and "Time". I would like the CommandButton to retain the most recent entry from a user and delete all previous entries.
My master sheet is set out as such...
A, B, C, D,
UserID, Date, Time, Response
The users could potentially submit multiple entries on the same day. Ideally I would like to be able to click a CommandButton and delete each user's submission but retain their most recent one (based on "UserID", then "Date", then "Time").
I've searched all day for a solution and I've come close but I can not figure out a code that accounts for my three variables ("UserID", then "Date", then "Time").
View 5 Replies
View Related
Mar 3, 2008
I am working on a sales sheet for my business. I have a worksheet that has the names of everyone in my store that has sold anything in column A. I want to create a list that has just my full time sales people and will delete everyone else.
View 9 Replies
View Related
Dec 28, 2009
i am getting a run time error 1004 Delete method of worksheet class failed,
ws.Delete
Sub Save()
Dim myName As String, myFolder As String, e
Dim fso As Object, temp As String
ThisWorkbook.Save
Set fso = CreateObject("Scripting.FileSystemObject")
myFolder = "C:UsersRecsDocumentsTestVBA & ExcelEmail_Files_Temp" & Year(Date) & "" & Format$(Date, "mmm")
For Each e In Split(myFolder, "")
temp = temp & IIf(temp = "", "", "") & e
If fso.FolderExists(temp) = False Then fso.CreateFolder (temp)
View 9 Replies
View Related
Feb 19, 2010
I've been trying to copy values from one range to another, and the ranges need to be dynamic. Normally I use a combination of the 'range' and 'cells' properties, like this:
View 2 Replies
View Related
Apr 26, 2013
Any way of deleting all rows not containing particular text? I did it as a loop but it took forever and I had to run it several times because it kept jumping over values. There must be a simpler way, I just can't think of it yet! still new to VBA.
Dim DateColumn As Range
Dim cell As Range
Set DateColumn = ActiveSheet.Range("a2:a623")
'remove unwanted rows (not = M XXXX MTD)
For Each cell In DateColumn
If Not cell.Value = "January 2013 MTD" Then
cell.EntireRow.delete
End If
Next cell
View 7 Replies
View Related
Feb 22, 2014
I would like to empty cells (not delete a row) that contains in column B values that are like Paper. In this cells are different materials (PVC, paper, steel, wood, glass) and also paper have different names (Paper 50g, Paper 80g, Paper 120g,...). I want that all that cells in column B that contains (Paper) are empty.
Before:
A
B
1
548787
Paper 80g
2
646411
Steel bar
3
634211
Steel pipe
4
521446
Paper 300g
5
521424
Paper 180g
6
...
After:
A
B
1
548787
2
646411
Steel bar
3
634211
Steel pipe
4
521446
5
521424
6
...
View 7 Replies
View Related
Apr 3, 2014
code that will find the last row of data, delete all cells, and move them to the left.
Please see below:
BEFORE
After
1
ABCD
Frank1
[Code]...
View 3 Replies
View Related
Feb 10, 2008
I have a workbook with parantheses in each cell. The contents are text and an example is;
Basketball-WNBA (71)
I want to delete the (71) from every cell. The numbers are NOT the same but will always be at the end with () around them. How can I do this without going to every cell? There are about 40,000 cells in the workbook..
View 9 Replies
View Related
Jul 8, 2007
I have used Vlookup to search and to return specific values to column 77. For those values that are not defined, Vlookup will return #N/A ( null value). Can I know how to write a macro that can go through each cell in column 77 and delete those cells that have null value? I tried the following code but it doesn't work
Sub clear_contents()
Dim rwindex, colindex
colindex = 77
For rwindex = 658 To 663
With Worksheets("Invoice").Cells(rwindex, colindex)
If .Value = Null Then
Worksheets("invoice").Cells(rwindex, colindex).ClearContents
End If
End With
Next rwindex
End Sub
View 6 Replies
View Related
Dec 19, 2008
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View 2 Replies
View Related
Jan 22, 2013
I have manually highlighted a large magnitude of cells (I would have tried some sort of automation but there isn't really a pattern unfortunately). The cells that I did not highlight are useless to me, and I would like to get rid of them. Is there any way to delete all cells that are not highlighted on a given sheet?
View 2 Replies
View Related
Mar 28, 2013
Is there a way (non vba preferred) to set up a button that will delete values in a set of 10 or so cells. I make hard fill updates to a tab each month and it would be more effecient if I didn't have to go through and wipe all the old information out.
View 2 Replies
View Related
Jul 25, 2008
I am trying to write some code that will compare three cells on a row and if they match then it will delete the row.
Column U-has Y, N entered
Column V-has Y, N entered
Column O-has 1, 2, entered (some cells may have the fill color set to red)
I need the macro to look at Column U and Column V and for example if the cell U2 has a N, and V2 has a Y then the macro would look at O2 and it there is a 1 with the fill color set to red the macro will delete that row.
The spreadsheet will vary on how many rows it contains. It can contain up to 5,000 rows and the macro would need to go through all the rows.
View 9 Replies
View Related
Aug 12, 2008
i am going to have 25 sheets for subcontractors in a construction magement workbook. i want to delete 7 cells on each of the 35 sheets. they will all be in the same place on all sheets. ex c7 g18 e14 etc on all 25 sheets. i was wondering if there is a way to create a function that can perform this operation.
View 13 Replies
View Related
May 15, 2009
Is there a way using a macro to check each row in Column D and Column F for "0", so that when both columns in the same row have "0" and only when both columns in the same row have "0" the contents "0" in that row of Column D and F will be deleted?
View 2 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
Nov 20, 2011
Up till now I have been using the code posted not realising it fails in certain conditions, The data in column A can contain what look like blanks but are not and therefore do not get removed. Can any tell how to get around this problem, as wish to delete those as well
Code:
Sub GetRidOfBlanks()
Dim RO As Integer
' GetRidOfBlanks Macro
' Macro recorded 21/07/2011 by Peter Hayward
'
' Keyboard Shortcut: Ctrl+Shift+B
[Code]...
View 1 Replies
View Related
Jan 8, 2012
I need to delete cells that contain certain characters (symbols) and space. Say I have some columns/rows like -
Column 1 Column 2 Column 3 Column 4 Column 5 Text Box Text Box Power Power Place Nice Nice.Plus Jump Jump Over High (Jump) Car Black Car Green.Car Car's Color Cars Blue Color Green Red (Color) Black White
And now I need to have cell without those contain symbols or space or character like -
Column 1 Column 2 Column 3 Column 4 Column 5 Text Box
Power
Nice
Jump
[Code]....
View 7 Replies
View Related
