How To Delete Unnecessary Rows?
I want to delete unnecessary rows in a 332324 row s/s containing 30000+ ranges of data of differing sizes.
The minimum necessary requirement for each range is 4 rows. There should also be one blank row between each range.
Does anyone know code which will carry out such an instruction please?
View Complete Thread with Replies
Related Forum Messages:
To Clean Out Unnecessary Data From A File
I need to clean out unnecessary data from a file, (see this example text file Link),
I'm not sure how to go about this in excel,
basically every file starts with 9 cells that needs to be deleted, then two cells of real data then one with garbage that needs to be deleted, it goes like that for 40 cells,
then again 10 cells of garbage that needs to be deleted, then 40 of real data and goes like that up to 3000 lines,
I know it sounds confusing but if you take a look at linked file from above it will make more sense, (to save it right click on "clean up.txt" and then save target as)
at the end I need to have all cells full of data
How To Recognize Unnecessary Reference Files In The Vbe
I made an excell document with many many vba codes/procedures. While creating it, I was doing some tests/playing with the references in the visual Basic Editor.
My problem is now I dont know which ones are necessary and which dont. I want to remove any unnecesarry reference. How can I know which ones are necessary for my project to run properly and which dont?
Delete & Merge Columns,Delete Rows With Filter, Etc
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
Delete Blank Rows & Rows Below Meeting Condition
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
Delete Rows Matching Criteria & Move Rows
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
Delete Rows Based On Criteria & X Rows Below
This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341.
Column_To_Check = 1
Start_Row = 1
End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row
Search_String = "SA341"
For Row_Counter = End_Row To Start_Row Step -1
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then
Delete Blank Rows (formula Not Deleting All Rows)
I have the following codes to delete all blank rows in column A
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
Consolidate Matching Rows And Delete Duplicate Rows
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
Delete The Columns First Then Rows Will Not Delete
Is 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.
Delete Rows Based On Values In Rows Below
I am copying a price list from a worksheet. I currently have a script that deletes unwanted rows (products) but these products' header rows' are left. I also want to delete these text based headers. One solution might be a script that reads a columns cell value in the row(s) below and if values are missing the header row should be deleted.
Delete Rows: Macro Skips Rows
Need to solve my problem in the thread "Type Mismatch Error Message". Now a new problem has come up in the same code, so - according to the rules - I've started a new thread. (This one is most likely due to my poor knowledge of VBA syntax).
Dim i%, j%
Dim Nr%, valid As Boolean, BYPdata As Boolean
Dim ar1 As Variant
Dim ar2 As Variant
Dim ar3 As Variant
Dim ar4 As Variant
Nr = 20
ar1 = Array(11, 14, 19, _
20, 22, 25, 26, 27, 28, 29, _
30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, _ .................
Delete Rows Same As Rows On Another Worksheet
I have a workbook where I import data from another workbook. This might get done several times a month. As I go through the imported data I delete rows not needed which gets place into another worksheet. What I need is when I import data again is a macro that the worksheet where my already deleted rows are and compairs them to the new data imported and if the first 13 columns of data matches it delest them off the imported sheet.
I found some code which I altered at [url]
and posted the same question but haven't got any response as of yet.
So the code that i go works but only if the row matches in the exact order on both sheets so e.g. (if row 1 on sheet1 matches row 1 on sheet2) it works but if (row1 on sheet1 matches row 3 on sheet2) it does not.
Here's the code I have so far.
Public Sub delOLD()
Dim i As Long
Dim iLastRow As Long
Dim ws As Worksheet, ws1 As Worksheet
Set ws = Sheets("Data") ' Imported Data
Set ws1 = Sheets("Old Records") ' Deleted Data
'ws1.Visible = xlSheetVisible
Delete Entire Rows If Rows Below Has Value
I have a sheet as shown in the picture below. If the cells in Column N has some values that are the result of some calculations. How ever if there are more than one of it, the entire row has to be deleted.
Eg:The value in Column N 816.323 has 3 rows below each other.Only one has to be there.There can be upto 10 values one after other in this way.
I tried to write a progrma by using loops and cehcking if ther is value in the cell,go to cell below and if there is value,delete the row in a loop of 10 times.But it didnt work
Delete Rows If NOT Contains
I want to delete rows if any given cell in A column do not equal values A B M or O
This sounds so simple, can someone remind me how to do this---I don't think I even need to use objects...
Dim LastRow As Long, r As Long
Dim objNAME As Ojbect
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For r = LastRow To 2 Step -1
If Not objNAME.Value = "C" Or _
objNAME.Value = "B" Or _
objNAME.Value = "M" Or _
objNAME.Value = "O" Then
Delete The Rows
I really want it, so that it will delete what is in row 6, right down to the 2nd last one with something in it (because i have a border ending on the last one.
Delete Rows With #value
I have a large sample of data,
approx 2000 rows across about 25 colums.
In a number of cells there are #VALUE! or #DIV/0!
Is there a quick way to delete any row that has one of these entries
in any of the 25 columns.
Data must also stay in current order.
Delete All Rows Except These
The MS documentation was a mouthfull to i might add
I need to delete all the rows EXCEPT those containing a list of 10-15 names in column D.
The function should not be case sensitive, as some of the names are
in capital letters, and some are not.
really cool would be if there's a messagebox telling "No names found",
as all the rows then would be deleted.
Delete Rows And More
I have a large design table that I would like to copy the values, open in a new workbook, and then delete any rows with the value of "DELETE" in any column. This is a process that will be repeated many times. The columns with the "DELETES" will be different everytime. There may or may not be mulitple columns with "DELETE"s. The macro needs to be contained in only the original workbook but the actions taken while making the new workbook. Please let me know if this is even possible.
Delete Three Rows
The following code tried to delete the rows. If in column F, there are two consecutive cells are empty; And for one cell in column G, relative to cell in column F, if the cell is not empty, Then delete the three rows: r, r-1 and r-2. But the code does not work.
For r = DataLastRow To 8 Step -1
If IsEmpty(Range("F" & r)) And IsEmpty(Range("F" & r - 1)) _
And Not IsEmpty(Range("G" & r )) _
Delete Rows Which Do Not Contain Certain Text
I have a spreadsheet which is created 2/3 times daily and I need a macro to do the following;
The columns are A To AJ, I need to delete all columns with the exception of columns M, N, R
Column M contains currency (GBP, Euro)
Column N contains dates
Column R contains various agent log ins, such as LI111222AB, LI222111JM, IFCTest & HOTAYLORL
The row number can vary (Average 1,000), I need to delete all rows that do not contain a log in which begins with LI in column R
Once this is done, subtotal by column R (agent log in), column N, & column M
Vba Delete Rows Above First Used
I'd like to find the first used row in a worksheet, and delete all the rows above it.. ie all the empty rows..
To show i am trying, yes i know very trying, i've got this far to find and select first used cell ( code from here) but can't work out how to select the area above..
i've been looking at from r = 1 to FR.Delete, thinking this would do it.. but sadly
Dim Rng As Range, rcell As Range
Set Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each rcell In Rng.Cells
If Not IsEmpty(rcell.Value) Then
FR = rcell.Select
Delete All Rows Except For Row 1
I wanted to select some criteria based on an AUTOFILTER and then have a macro delete all rows from 2 to the bottom... then SHOWALLDATA pick another set using AUTOFILTER and then having excel repeat and delete everything 2 down... the problem i'm finding of course is that when you AUTOFILTER row number 2 is not always at the top, so I need some relative I think..
Delete Selected Rows
I'm trying to write a macro that if a 1 appears in column N (1 may appear more than once & the sheet continues forever) then to hide that particular row.
I will also then have a seperate macro to delete the entire rows where 1 appears.
Move And Delete Rows
I am trying to create a macro the looks at column A and moves the entire row to another worksheet or deletes the entire row based off of the value in each cell. Right now I have the Macro replace the downloaded values to either AG, G, GI, ICP, IMG or delete. The idea be to move all of the values with AG in column A to the AG worksheet, move G to the G worksheet, etc.. and then delete all of those with delete in the cell.
Automatically Delete Rows With Nothing In Them
I have a spreadsheet where I would like to automatically delete rows with nothing in them. Here is a typed up example:
row 1 99999
row 3 99999
row 5 99999
row 1 99999
row 2 99999
row 3 99999
The spreadsheets I am dealing with have 100's of rows so it is difficult to search through the spreadsheet and delete the rows with nothing in them.
Delete Blank Rows ..
I am trying to write a macro that will check from 1 to 143 columns..and if all the columns are empty then it has to delete that entire line. Totals rows are over 35000. I am using excel 2007. I have written the below code. Could someone pl help me in enhancing this.. or a better way as this is taking about an hour to complete.
Dim i As Long
Dim j As Integer
j = 2
For i = 2 To 37735
loop1: For j = j To 143
If Cells(i, j) = "" Then
j = j + 1
j = 2
loop3: Next i
Delete Rows After Certain Criteria
I have the following table, which starts in Column A, row 2. I would like to have a macro, that deletes some rows after certain criteria.
If Column F = SO normal and
Column E = aa, bb, cc, dd, ee, ff then delete the whole row.
GeoRgn refSub Rgn refCountry Country 2GroupENNR3NxaaSO claim-inENNR3NxbbSO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3NxaaSO normalENNR3NxbbSO normalENNR3NxccSO normalENNR3NxddSO normalENNR3NxeeSO normalENNR3NxffSO normalENNR3Nx111SO normalENNR3Nx111SO normalENNR3Nx111SO normalENNR3Nx222SO normal
Delete Unwanted Rows
1. In excel Sheet how to delete the unwanted rows in a proper way
In the following Data :
Day Date A/c Time Code Description
Tue 08/07/10 1708 02:54 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 1708 02:55 R130006 > Reset [ri 0: Residence]
Tue 08/07/10 1708 02:56 R301000 > AC Restoral [ri 0: Residence]
Tue 08/07/10 1708 03:00 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 1708 03:40 R301000 > AC Restoral [ri 0: Residence]
Tue 08/07/10 1708 04:08 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 2410 04:23 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 2410 04:47 R130006 > Reset [ri 0: Residence]
Tue 08/07/10 2410 05:56 R301000 > AC Restoral [ri 0: Residence]
Tue 08/07/10 2410 06:21 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 2410 06:30 R130006 > Reset [ri 0: Residence]..............
Delete Rows Of Other Sheets Also
I have a WB where the first sheet is "Introduction", second is "Master Data" wherein the name of players with some details are kept.
After that some sheets are there which are linked to "Master Data" ie say Sheet2,sheet3,Sheet5 (sheet 4 is not linked to "Master data" but appears after sheet3)
I want that if a record , say record 3 at row 5 is deleted also delete that record 3 in other linked sheets ie Sheet2,sheet3,Sheet5 The positions of the records differ from sheet to sheet ie if record 3 is at row 5 of master sheet it coulsd be on row 8 of sheet2, at row 10 of sheet3 & at row 6 of sheet 5. All the name of the person are linked to sheets with =masterdata!B5 like this.
Faster Way To Delete Rows
My spreadsheet is a contract file that includes a list of part numbers being sold. Each part number refers to a lookup table of ALL about 20,000 valid part numbers and prices. Once the contract is finalized, I want to make the contract sheet smaller by deleting all rows in the lookup table that are not required for this contract.
The following code works, but it takes more than 10 minutes to work through the complete list. I read down the lookup table. For each record in the lookup table, I call a routine that reads through an array of the part numbers that are included in this contract. If the lookup table part number IS included in the contract, I skip it. If it is NOT required, I delete it. I then return to the main lookup table and read in the next lookup table record.
This is the main routine where I progress down the big lookup table.
'Work down the Price File range from top to bottom
Set RefTableRange = DSWPrices.Range("DSWPriceRange")
RefTableIndex = 1
Application.Calculation = xlCalculationManual
While RefTableIndex < RefTableRange.Rows.Count
RefTableIndex = RefTableIndex + 1
'check if this part number is included in the contract
Call CheckRefTableRow(RefTableRange, RefTableIndex)
Delete Rows Containing Text
I am trying to write a code that would remove rows with any sort of letters in them. I have looked up other ways of deleting rows, but I cannot find the script that would encompass all (alphabetical) letters.
Delete Matching Rows
Situation: I would like to compare the information between two worksheets and delete the rows that contain the same data in multiple columns, on a row by row comparison.
IE: I have two worksheets, each have identical row headers, with 5 columns each.
Company Load Date Load Time Load Description Amount ?Report?
Store#44 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1
Store#44 5/14/2009 02:34:21 AM SLATOUR $20.00 WS1
Store#45 5/14/2009 01:55:41 AM GCHANDLER $100.00 WS1
Store#46 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1
If column A(Company), B(Load Date), and E(Amount) for record 35 in worksheet one, match the same columns for a record in worksheet two, both records are deleted/highlighted/marked with an x in an additional column/anything.
Alternately, I can combine the data in both worksheets into one large worksheet, if that would make the solution easier. And or adding a column that idenifies which record came from which report.
Basically I have two similar reports; each contain a few rows of transactions that the other does not, I need to separate the matching transactions from the unique transactions, in order to balance the two.
I have tried using the Remove Duplicates function but it saves one of the matching records (they should add an opiton to delete matching records aswell keeping only truly unique records), I dont understand how to work Conditional Formatting to get it to do what I want, I dont know macros, or vlookups.
Delete Both Rows If Cell Value Is Same
I am reconciling a bank statement. My numbers in column "A" are positive, The bank numbers,also in column "A", are negative.
In Column "B" I put an if statement- , If(A1< 0,A1*-1,A1), to get all entries to positive numbers and copied down.
I sorted the numbers in column "B" and now need a macro to delete both rows if they have the same value.
The total of column "A" is say $500.00 with 50 entires. After the deletion column "A" is still $500.00 but with only 10 items because all duplications were deleted resulting in my bank reconciliation.
DeletE All Rows In A Dataset
I am looking for a macro that is capable of deleting all rows in a dataset where the following statements are not true: column B is equal to "OP00" (o, p zero zero), the left hand character of C is equal to "L" and D is equal to "CC", as in the scenario below. I basically need to keep all rows which match the structure below, i.e. to clean the data.
B C D
To Delete My Unused Rows
Apparently Excel can't delete unused rows, nor can it specify the sheet to be exactly X by Y large. My problem comes in any time I fill down a column of data by selecting the column header (A for example) and selecting fill down, rather than selecting the exact range of cels I need the formula in (which might be several thousand...) Excel winds up taking my sheet that was, let's say, 1000 rows long and filling down until now it's many many many thousands of rows long, with "error" fills in most of the rows. This now means that any kind of sorting, resizing of rows, etc. takes absolutely forever because it's dealing with 5 or 10 times more rows than I am actually using. Even clearing the contents of all the extra cels does not help. I can't in any way seem to get rid of these extra rows.
Vlookup Delete The Rows
as i look at examples all over the place for this... im not sure why my code is giving me an error...
Dim lookForRng As Range
Dim lookFor As Range
Dim rng As Range
Set lookForRng = ThisWorkbook.Sheets("Sheet2").Range("C3")
Set rng = RS.Sheets("Sheet1").Columns("A")
For Each lookFor In lookForRng
found = Application.VLookup(lookFor.Value, rng, 1, 0)
If Not IsError(found) Then
I'm getting a "delete method of range class failed" on line "lookFor.EntireRow.Delete" but... im not really sure why its failing
Delete Rows Without Specified Text
What i'd like is a macro that will delete all cells that don't contain the word pagelibrary somewhere in the cell. As these are weblogs, pagelibrary can be in many different places in the link due to redirects etc, so the macro would have to be flexible with that word. All cells are only one column, in column A. Also if it is possible to delete everything before the word pagelibrary that would be helpful as well as some of these links are very long (and possibly duplicates due to capitalization and redirects).
I've done some searching and this code is the code that closest matches what I want to do. I am by no means a VBA expert; I can decipher a little of what code does, I just can't write it .
‘Hold Shift Key To delete rows Not equal To
Application. ScreenUpdating = False
Dim currentRow As Long
Dim lastRow As Long
Dim activeColumn As Long
Dim activeValue As String
Dim currentValue As String
lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
activeColumn = ActiveCell.Column
activeValue = ActiveCell.value ................................
Macro To Delete The Rows
I have a table of data A2:H1000 (this gets longer every week). Within that data every so often i have a row which is blank from A:G, but has a value in H.
I want to create some code that checks the rows and if the cell A in the row is blank, to delete the entire row.
Delete A Set Of Rows
I am making a resource database at work. we do not have access, we have excel 2003. I need to pull name, address, phone # from a variety of sources and organize it so we can print letters, lists, labels etc. Often the data for on contact takes up multiple rows when you do a large paste. I figured out how to get it all in one row(I use =trim(cell) to place the cell where i want then copy the fuction to all rows I need for each column. then I copy and paste special =values to remove the cell reference) but this was leaves rows with garbage data.
(if ir takes up 2 rows it will be every other row that needs trashing, 3 rows the first row is good but the next two are trash, etc)
Is there a way to get excel to automatically remove the junk rows(ie if i select the section Im working on can I get excel to remove every second row?) or is there a better way of doing it so the junk rows dont happen?
Delete Rows With Cells < #
I'm trying to create a macro and having absolutely no luck with it. I want the macro to run through all the rows and delete all rows where every number in that row is less than a specified number (.03 for this particular case).
Delete Rows By Marco
I have a cell wich contains "1234". Now I want to look if the value of an other cell 1, 2, 3 or 4 is. Isn't it then should that row be removed.
1234 could also be 124 or 1 or 4 or 6 or ....
To Delete All The Rows That Do Not Have A Unique ID
This is an example of my table , there are more columns but here is apart of it:
id Product Name
1Frnt Fndr CR125/250 black
1Frnt Fndr CR125/250 white
1Frnt Fndr CR125/250 silver
1Frnt Fndr CR125/250 00 cr red
2Frnt Fndr CR125/250/450/500 black
2Frnt Fndr CR125/250/450/500 white
2Frnt Fndr CR125/250/450/500 00 cr red
3Frnt Fndr CR125/250/500 black
3Frnt Fndr CR125/250/500 white
3Frnt Fndr CR125/250/500 florescent red
4Frnt Fndr CR80/85 black
4Frnt Fndr CR80/85 00 cr red
I need to delete all the rows that do not have a unique ID:
Frome the table above I need it to return something like this
id Product Name
1Frnt Fndr CR125/250 black
2Frnt Fndr CR125/250/450/500 black
3Frnt Fndr CR125/250/500 black
4Frnt Fndr CR80/85 black
check out the file
Delete Rows On Conditions
I would like to delete rows that are based on these conditions:
First ,Do a loop from row 2 to last available row
- Delete rows with same column(column B) that has the same value. However I want the last available row with the same ID to remain.
- Delete Rows with any values in found in other sheet column B. When the value taken from the first sheet(ABC) is compared to the column B in second sheet(DEF), if they are equal, the row will be deleted in the second sheet and the rows in first sheet will main.
The comparsion of ID are compared between sheet ABC and sheet DEF
(the Highlighted are those rows to be deleted based on those conditions)
The final outcome are shown on col H I J for sheet ABC and col I J K for sheet DEF.
100222Case 33100222Case 3
50333Case 1450333Case 1
57444Case 3557444Case 3
150666Case 26200555Case 1
100111Case 17150666Case 2
100888Case 38100111Case 1
231999Case 19100888Case 3
200555Case 110231999Case 1
100112Case 411200555Case 1
100113Case 112100112Case 4
100114Case 2 13100113Case 1
100115Case 114100114Case 2