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 RepliesI have an accounts spreadsheet that I copy and paste customers names and addies into from the website back end sales information.
I do not copy e-mail addresses.
I have a mailto: with an e-mail address appear in the file in lots of places, it seems I delete it from some cells and it appears in others, my file is infested with the things now.
I can delete one by one, but this would take me weeks any ideas of how I can ctrl a select all cells and mass delete these things.
I am face with making a brand new accounts file which is a lot of work.
1. Remove J,K,N,A Columns,
2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy
3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value
4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only
5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
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 RelatedIs there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
View 12 Replies View RelatedI 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.
I am attempting to delete rows with columns not containing "MC". In the example below, the result should be 3 rows remaining (1,2 and 5):
Y
Z
AA
IP/DA/SV
MC/SWL/SA
MC/SH/SA
[code]...
Instead, all I get are results with Y starting in MC, in this case the last row, all the other rows are deleted. I need to get all rows that have MC in three columns (Y, Z or AA) and delete the ones that do not. In this case, rows 3 & 4.
Below is the code I have been using:
Sub Del_Rows()
Application.ScreenUpdating = False
With Range("Y1:AA1", Range("Y" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="*MC*"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub
Seems simple enough but this newbie chump is stumped why it is not reading the other columns
I tried to modify this code for my next problem: to delete entire columns where the cell in row 2 is empty, but it doesnt work. The code I use is:
Sub test2()
'This code will create a copy of the current sheet, and retain only the rows
'that have the value '1' in column B.
ActiveSheet.Copy Before:=ActiveSheet
Application. ScreenUpdating = False
On Error Resume Next
Dim ir As Long, mrows As Long, lastcell As Range
Set lastcell = Cells.SpecialCells(xlLastCell)
mrows = lastcell.Row
'Note rows are deleted from the bottom going up
For ir = mrows To 1 Step -1
If Len(Trim(Range("b" & ir).Value)) = 0 Then
Rows(ir).Delete Shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub
The ideal solution would be not a second macro but one that does firstly delete the rows and then the columns.
Is there a way to delete x # of rows in columns b, c, d, g, j, m, p & s.
I regularly enter data in these rows for comparisons and then need to delete the data with out deleting the formulas in e, h, k, n, q & t.
A macro or one key then delete would be great.
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").
I am trying to delete all the rows that have blanks in both columns "D" and "F".
VB:
With Sheets("2012")
For k = 6 To 1000
If (.Range("D:" & k) = "" & .Range("F:" & k) = "") Then
[Code]....
I have a single workbook with 55 worksheets. I need to delete rows #1-14, then delete columns A & B on all worksheets. The macro below seems to be running in a loop which appears to delete rows 1-14 and columns A & B until there is no more data left on the active sheet only. It does not run on any of the other sheets - even if I select all sheets before running the macro.
View 4 Replies View RelatedI have two columns. In the first column, there is in every cell some text. In the second column, there are some blank cells. It looks like this
textCOL1 textCOL2
text1COL1
text2COL1 text2COL2
text3COL1 text3COL2
text4COL1
text5COL1
text6COL1 text6COL2
How do I remove the rows which have text only in the first column?
The output should be:
textCOL1 textCOL2
text2COL1 text2COL2
text3COL1 text3COL2
text6COL1 text6COL2
I have a large spreadsheet that is 65000 rows and colomns A thru W.
I need a macro to delete rows that if Date and Account and Type are
equal and the net of Quantity is zero delete those rows.
Col A = Date
Col C = Account
Col D = Type
Col J = Quantity
What the macro would do is if an account had 250 rows of activity on
say 7/7/2004 (Date) and same type of account and the net of all
Quantity is equal to zero delete those rows.
I would like to delete all unwanted rows and colomns. I know this can be done but Old Age is catching the grey matter
I want to delete all except the following ranges, Range("A1:H27").Select
I have a spreadsheet that in column A has either the letters "C", "LP", or is blank.
In Column L it has either numbers 1 through 100, or the letter "D".
What I need it to do is to check and see if the data in column L is "D" and in the same row column A is blank, then delete that row.
Also, if the data in Column L is "D" and the Data in column A is either "C", or "LP", then I need it to change the "D" to "1".
It might be easier to do it separately because once you run the part that deletes the rows that don’t have a “C” or “LP” then I could probably just do one that says if column L = “D” then change to “1”.
Lets see if I can simplify this for you.
If Column L = “D” and Column A = BLANK then Delete Row
(this would delete all “D’s” that don’t have the “C” or “LP”)
THEN
If Column L = “D” then change to “1”
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
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 Relatedtwo things on the attached sheet.
First problem: in column B, I have a formula based on contents of column A. However, the limitations of 7 nested IFs leaves me with errors - I need 3 more IFs. Is there a way around that?
Second problem: I need to show the row of the first (earliest time in column A) and the last (latest hour in column A) record for each name. I've been sorting by name and time, then manually deleteing the rows I don't need. I know there's a better way, but I can't find it!
ertret.jpg
I am trying to delete rows that contain the same addresses BASED ON COLUMNS.
Do you see how in columns C and D they all match EXCEPT for row 4? How do I delete allllll the rows that when c and d match.
I want to keep the ones that DO NOT MATCH.
P.S I HAVE 4000 ROWS I WOULD HAVE TO GO THROUGH
I need to filter the following file.
I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.
The end result should look similar to columns F,G and H!
I have command button in Sheet 3 (from where I will be running the macro) and I need a macro which will delete all the blank columns and rows in the data containing in Sheet 1.
View 2 Replies View RelatedI have a workbook, with a worksheet "Summary". In this worksheet "Summary" I would like to delete all columns that have less than 100 rows.
Please see the attached file : Summary.xlsx‎
I have a spreadsheet with hundreds of rows. Columns C and D contain either TRUE or FALSE. I want to be able to automatically delete the rows where both columns are FALSE.
View 1 Replies View RelatedI'm trying to come up with a formula to find the last row without any data in columns E and F and delete the 10 rows below them. Is there a way to do this?
View 3 Replies View RelatedI am trying to use a macro to delete 2 columns from a spreadsheet using a macro.
This sounds easy, but due to the fact the columns needing to be deleted ‘cross’ merged rows the macro then deletes all the columns based on the merged cells.
In the attached spreadsheet (ColDel) if you manually highlight columns D and E and then use Edit/Delete this will delete columns D and E correctly.
I need excel to delete entire rows if there are duplicates in two columns combined.
So, if I have this sample data (in reality I have a lot more columns)
1 Tr. ID Last Name First Name Sex
2 RBM_A Miller Johnny M
3 RBM_A Bayron Josephine F
4 RBM_A Jackson Reynaldo M
[Code] ........
I want excel to delete only row 9, not 11, 13 or 14.
So far, if I look for duplicates in both columns it marks duplicates in either one. Also if I delete the duplicates, it deletes them only in those two columns, leaving me with distorted data (in this example, row 14 would have no last or first name, but only the values "RBM_C" in column B and "F" in column E.
How do I delete duplicate rows in a sheet using a macro. When I say duplicate row, it is not based on a particular column but all the columns, so it is a true duplicate record.
View 9 Replies View RelatedI am looking for something that will first delete all empty columns up until it runs into data, then delete all rows that are blank in Column A until it runs into data.
In other words, I recieve different spreadsheets that start their data in different locations and I would like to have them always start in cell A1.