Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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]..............

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Filter Then Delete Unwanted Rows
I am using the following code to filter for data I do not want and then to delete those rows and show remaining data. It works fine except when the filter comes up empty and there is no unwanted data to to improve this code to accomodate this situation?

Selection.AutoFilter Field:=4, Criteria1:=">" & dweekend, Operator:=xlAnd
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Thank you for reading

View Replies!   View Related
Delete Or Remove The Unwanted Pages
I have a 'document' which shows 294 pages, I have only 10 actual pages. How
can I delete or remove the unwanted pages?

View Replies!   View Related
Delete Unwanted Characters Within Certain Criterias
I have a spreadsheet that i manually edit each and everyday e.g.

WWEX/UQ bbr263666
LWL/KL/B 270407
LNL/SB/UB HLC - 271955
SMMQD/WT HLC - 269516
NILVA/UC EUi273645

For everything that doesnt equal EM, LN, LW and TH in column A, everything should be deleted in column B.

For the remaining EM, LN, LW and TH, i would then like it to delete / (forward slash and all characters after this) so that this would make my life easier.

View Replies!   View Related
Autofilter To Delete Chunks On Unwanted Data
I am using an autofilter to delete chunks on unwanted data, by using Range(Selection, Selection.End(xlUp)).Select however it picks up the header row. Is there anyway I can either get the selection of data to select one less line, or a way to get it to leave the header line?

View Replies!   View Related
Deleting Unwanted Rows <10
I need some VBA to delete rows that are under the value of 10 in column H and keep the rest, is there a way of doing this

View Replies!   View Related
Automatically Remove Unwanted Rows
Im not strong in excel, that why Im here. So:

I work every day with big amount of item numbers and lists in excel and I need some macro or code to automatically remove rows, containing unwanted text.



So I paste the text from my database, and want to automatically delete rows containing DIMM string. How can I do it?

p.s. is there also a way to leave only the rows I want?

View Replies!   View Related
Removing/Greying Out Unused/unwanted Columns & Rows
Need to find out how I can restrict a worksheet to show only the columns and rows that I want. For example, I want to show columns A to H, and rows 1 to 25, and nothing else - I don't want there to be a column G, or a row 26, just blank grey space. I know it's possible, because I've seen it done : D . But the closest I can get to is: View > Page Break Preview, which isn't quite what I'm after.

View Replies!   View Related
Text File Import Produces Unwanted Blank Lines Between Rows Of Data
After doing some searching I found a macro here which imports multiple text files into one workbook, keeping each text file as a seperate worksheet, each worksheet named the same as the original file name (minus the .txt extension).

However, it adds a blank line between each line of text. So I get this:

Pinging with 32 bytes of data:

Reply from bytes=32 time=232ms TTL=62

Reply from bytes=32 time=62ms TTL=62

Reply from bytes=32 time=65ms TTL=62

instead of this:........

Pinging with 32 bytes of data:

Reply from bytes=32 time=232ms TTL=62
Reply from bytes=32 time=62ms TTL=62
Reply from bytes=32 time=65ms TTL=62

This doesn't happen when I use the import wizard, or when I just open the file from the File -> Open menu.

My text files are always the same format (number of lines, etc.), so it's easy to delimit and insert forumlae as needed, but those extra lines are breaking a macro I've previously written to parse the data.

View Replies!   View Related
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

respectively )

View Replies!   View Related
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.

View Replies!   View Related
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
MsgBox lastrow

With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
End If
Next t
End With

End Sub

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.

View Replies!   View Related
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).

View Replies!   View Related
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:.......................

View Replies!   View Related
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.

View Replies!   View Related
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.
Sub DeleteRows()
Column_To_Check = 1
Start_Row = 1
End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row
MsgBox End_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

End If
Next Row_Counter
End Sub

View Replies!   View Related
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.

View Replies!   View Related
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).

Sub Delete_invalid_rows()
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, _ .................

View Replies!   View Related
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

View Replies!   View Related
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

View Replies!   View Related
Unwanted Repeats
I am getting repeats when there should not be any. Here are the formulas and
below is a portion of the worksheet. You can see hhow the Section and Work
Order are repeating. It should only show a one time entry....

View Replies!   View Related
Getting Rid Of Unwanted Data
Each week I import some data into my workbook (its financial-imports stock names, identiy codes and stock type). I've added a formula which calculates the credit ratings of each stock type, and then conditional formatting to highlight any credit ratings which are less than my required rating.

Further to this I need to get rid off all the rows which which have an stock type "No Asset Class". I cant use auto-filter because there are too many other stock types which I need to be included.

View Replies!   View Related
Unwanted Breakpoint
While running my program, the code is halted and my code is displayed as if I had set a breakpoint. The next line of code to execute is highlighted in yellow. However there is no maroon colored background around the code. I have no breakpoint set on this line. Is there any reason my program is behaving this way? Is there anything I can do to get rid of this bug?

View Replies!   View Related
Unwanted Rounding In Math
I have a spreadsheet acting as a check register. In looking through the over 2300 entries, I am getting "odd" rounding issues. I have attached a small piece of the worksheet to demonstrate this quirkiness. As you can see, I am adding numbers out to two decimal places (which should result in two decimal place answers). The result for 11/11/2002 has become a more complex number. And, the result for 11/14/2002 goes back to two decimal places.

I have upgraded my office suite since 2002. Could that be an issue? I prefer not to have to "force" the rounding =round(result,2) to solve the problem.

View Replies!   View Related
Unwanted Statements In Macro..
I created the driver macro Mike2 (At first cosisting only of the first 2 lines) with keyboard shortkey +m, to run macro Mike. (In future additionals macros). Every time I run this macro it adds 2 lines to the Macro ,regardless of the End and Exit sub statements. Why?? Do not want this to happen.

View Replies!   View Related
Remove Unwanted Characters
i want to convert an excel spreadsheet into a text file, keeping the same format, but when i do so, excel puts " " around the characters, which i don't want. Example: please see the 2 attachments.

e.g. when i convert, i don't want the " " around the commas in the text file.

View Replies!   View Related
Unwanted ActiveX Warning
I have used the OWC.Spreadsheet 11 object in a form. But since then, I get a warning, usually when I open the spreadsheet, saying "This application is about to initialize an ActiveX Control that might be unsafe. If you trust the source of this file Select OK.etc,etc". I am using Excel 2003. Is there anyway of getting rid of this message? I am worried that users will see it, worry about using it and press cancel instead of OK at the prompt, thus rendering my form useless.

View Replies!   View Related
Removing Unwanted Duplicate Lines
Hi, I have a report that has multiple lines for the same job with notes of issues and solutions. Each line has a date assosiated with it.

What I need to do is to delete all of the notes apart from the first one. I have attached a sample of the data and highlighted the rows that need to be kept.

I have left the data in it's raw form as it is not always extracted in date order.

Can anyone help me out with a Macro that will sort the data and remove all rows apart from the earliest note per job number?

View Replies!   View Related
Removing Unwanted Text From Cells In VB
E122112 David Hall
Robert Townsend
Micheal Keel
Tanya Smith
Elizabeth Charles
E004587 Andrea Tummings

The problem is for those names that come thorugh with the Letter and than the number...I don't need the names like that....I would like a Macro that would look at each cell in column A and remove that from those selected cells. I would like my result to look like this...

David Hall
Robert Townsend
Micheal Keel
Tanya Smith
Elizabeth Charles
Andrea Tummings

View Replies!   View Related
Finding Unwanted Characters In Cells
I'm having the following little problem, and i'm hoping that
someone here can help me solve it.

I have a number of rows containing data starting at row 1.
I already made VBA code which finds the last row containing data.
That row number is stored in the variable "LastRowWithData".

What i want to do now is the following.

In column B (in row 1 to the row number stored in "LastRowWithData"),
there are long code's which all have to be checked for unwanted characters.

Some of these unwanted characters are the "I" and "O" characters both in
upper case and lower case.

If these characters are found a messagebox has to appear, saying something
like "Illegal character found in cell ____".

If more than one cell containing unwanted characters are found, either more
messageboxes with the cell coordinates have to be displayed or one messagebox
displaying all the cell coordinates in which the unwanted characters are found.

Im planning to use a while - wend statement to check all cells for unwanted
characters. (While ActiveCell.Row LastRowWithData)

Is there a simple way to do this in (Excel 2003) VBA ?

View Replies!   View Related
Removing Unwanted Characters & Moving Others
I'm needing some code that will remove an open quotation mark from the numerical value found in column A. This will be for every row with a numercal value.

example problem: "360015
example answer: 360015

All numbers will be 6 digits

And I've got another issue with numerals in column C. To the left side of the Cell there maybe a Number or Letter. To The Right side of the cell
there will be a single or double digit number. Both will be separted by some space (6-8 spaces). As in the examples below:
(again what you see on each line is in one cell in col. C.

R 59
E 6
0 4

I need the value on the left side of the cell to be moved concenate/merge with the values in column B.

There's will be approx 2000 rows that will need to be checked for this occurrance. Since there's so many rows,
I'd really would like to use VBA and not formulas to accomplish this.

View Replies!   View Related
Remove Unwanted Style In 2003
i have quite a lot of excel 2003 spreadsheets which once edited and saved by excel2007. now, i'm using back excel 2003. (because my efficiency dropped by half by searching the command i wanted in excel2007.) when i open them with excel2003 again, there are a lot of styles. i want to remove them. but it is a tedious job to remove them one by one for these spreadsheets.

View Replies!   View Related
Formula To Remove Unwanted Characters
I am looking for a formula that will remove any unwanted characters in a cell.

For example i may a word or string of words with "-" , " ' " or "," in them and i would like to have these removed.

View Replies!   View Related
Erasing Unwanted Text From Cells
I have 1800 rows of bibliographic entries that are I am cleaning up. The first column imported with some numbers and spaces that I don't want. Here is an example of the variation:

Abdalla, C.W.,
37 Abdalla, C.W., B.A. Roach, and D.J. Epp,
2 Abou-Ali, H and M. Belhaj,

I want all the rows to be like the first one, ie, no numbers and no spaces.

View Replies!   View Related
Unwanted Character, Can Not Search And Replace
I have a sheet that I often have to edit and import into SQL database.

I have no control over the format of the sheet given to me and it seems that something has changed causing me the following problem.

When I try to import the Data itís showing ten times as many items than are actually there.

The problem I think is in a text column. Iíve tested this by using an old column from a previous sheet and pasting this into the new sheet and I can import it ok.

What I seem to have is a rectangular box character but Iím not sure what it is.
I can copy and past it into Notepad as it is, but pasted into Word it seems to be a carriage Return.

So whether itís an actual charter or not Iím not sure.

Are there any tools out there so I can hover over characters in Excel to see what it is! So at least I can be sure what Iím trying to Search and Replace?

View Replies!   View Related
Avoid Unwanted Negative Numbers
I have a formula that converts the interval between two times into a whole number of minutes:

View Replies!   View Related
Exclude Unwanted Data After Macro Filter
I have a sheet for addresses (Column 1 = Name, Column 2 = Address line 1, Column 3 = City, Column 4 = Postcode) and I run a macro that Filters that data based on the the city to different Tabs. This works fine except that sometimes, once the data is filtered I will find one or two addresses on my new filtered sheets that I really wanted to exclude. As my main address Tab information changes regularly, Ideally, I would like to see the Item on the City filtersheet, Double click it, and this would have the effect of deleting it from the City filtersheet but also copying it to an "Exclusions" Sheet which I could use before I run my normal filter macro to filter out these bad addresses.

View Replies!   View Related
Unwanted Values/Items In ListBox Control
I have an issue with some unwanted items in a listbox that gets its data from a pivot table, however it only happens with the one pivot field and for three of its pivot items.

I tried following a suggestion from an earlier thread to re-create the pivot table, but it did not work.

I'd like to post the file, but its too large, even in zip format.

View Replies!   View Related
Macro Calls A Unwanted Procedure During Runtime
I have a VBA application in Excel.
In a certain time, when the code is executed in the

Private Sub CB_ValidaHoras_Click()

View Replies!   View Related
Romove Unwanted Entries From UserForm ComboBox
I have an worksheet with a UserForm. On this UserForm is a ComboBox with the RowSource set to a named range called lVender. This ComboBox is setup to post & sort new entries into lVender apon exiting the ComboBox.

If a mistake is made and it is not caught before exiting the ComboBox, then I need a way to remove the bad entry from the lVender list.

I have included a small sample to better show the problem.

View Replies!   View Related
Strip Unwanted Characters From Text String
Is there an Excel function that can strip a specificed unwanted character from a text string to leave all the other characters excluding the unwanted one?

e.g. if the original text strings were (in separate cells within a workbook):


The function would strip the "-" to leave:

N7D ...etc.

View Replies!   View Related
Clean Data - Removing Unwanted Characters
I have an excel sheet with approx 30,000 rows of data.

These are all keyword phrases.

They can be related to any subject, but for this example these are related to the root keyword phrase of "car Rent"

I have some software which basically pulls in keyword phrases from search engines and meta tags etc, including misspelled keyword phrases.

My problem;

The data is uncleaned.

In other words there might be I think it's called "Carriage return" data in there, so the row of data might be very deep (Instead of a row height of say 10.5 it could be anything, IE some could be 100 or 200 even).

There are unwanted characters, for example; ()[]{}+?!""^*

(If it could delete all unwanted characters except for letters/digits)
There is a problem I see, that if it removes _ or - between words, that it will join the words together which won't be of any use. If it deletes anything with a letter either side of it or a letter and digit, or 2 digits, 1 either side it would then need to add a space to replace the hyphen.

So for example; if there was a phrase in the list like ....

View Replies!   View Related
Looping Macro Generating Unwanted Results.
I have a spreadsheet with multiple departments on it set out in no particular order, (just as they are added) and this sheet needs to stay like that as a record of when added.

The problem I am having is that I need to generate a bordered blank Weekly, Monthly or 52 week planner for what ever department needs it on a different sheet so it may be printed. This will contain that departments items but leave the days/weeks blank for them to fill out.

As an example I use the code below to loop through the original sheet and generate a 52 Week Planner if the criteia matches.

View Replies!   View Related
Delete #VALUE! Rows
can i have the macro if column B found "#VALUE!" delete entire rows


in this example row 3 will be deleted

View Replies!   View Related
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

Sub FirstRow()
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
Exit For
End If
Next rcell
End Sub

View Replies!   View Related
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..

sample .....

View Replies!   View Related
Delete Rows With #N/A
developing a macro that will test a cell. If the cell displays "#N/A" then I want the row deleted. Then move on to the next cell.

View Replies!   View Related
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...

Sub DeleteExcept()

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
objNAME.Cells.EntireRow.Delete Shift:=xlUp

End If

View Replies!   View Related
Delete The Rows
Sub Delete_Schedule()
Selection.Delete Shift:=xlUp
End Sub

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.

View Replies!   View Related
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?

View Replies!   View Related
Delete Rows With A Value Less Than 5
I have a spreadsheet that is generated for me and it is not always going to have the same number of records in it.

I would like to have a macro that searches column C and deletes any rows that have a value less than 5

View Replies!   View Related
Copyright © 2005-08, All rights reserved