VB Code To Compare/delete Rows Of Data

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


Which Are The Variables In The Code, Delete All The Rows Which Have Data In?

Oct 28, 2008

This vba code will delete all the rows which have data in. At present it starts on A2 (so doesnt delete anything above A2. Which part of the code in red would I need to change for it to be A3 and which would I need to change for it to be B3.

View 5 Replies View Related

VBA- Code That Will Delete Blank Rows Between Data

Apr 1, 2009

a code that will delete blank rows between data, below is sample of my data, the header row is 8

From this ....

View 9 Replies View Related

Total Data- Having A VB Code To Delete Empty Rows

Mar 7, 2007

The spreadsheet I am working in has $ which I need to total. The information, or rows, can vary from day to day so there is no static place to have a cell with an @sum. Management wants me to add a pretty line that says

'Total' in one cell and the $ next to it. I have about 30 sheets with columns to total. Yuck, takes forever.

How do all of you total if you don't know the last row? At the top?

I thought it might work to put an @Sum in the last row of the spreadsheet, in the column

I need to total, and having a VB code to delete empty rows. That would be automatic.

View 9 Replies View Related

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

Compare Dates Against One Date & Delete Rows

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

Compare Columns & Insert / Delete Rows

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

Compare 2 Columns Delete Rows With Matching Values

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

Code To Delete Rows Based On Status Code In Column

Jul 24, 2009

I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".

At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):

View 4 Replies View Related

Compare 2 Columns & Delete Rows Ouside Scope & Show Formula Result In Message Box

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

VBA Code To Delete Certain Rows

Jan 22, 2009

I have an excel file that i need to delete some empty rows in the file. it looks like this: I have to delete THE EMPTY ROW above each custom table, and leave everything else as it is.

empty row
custom table
empty row
custom table
empty row
custom table

View 11 Replies View Related

VBA Code To Delete Rows

Apr 30, 2014

I am looking for a vba code that will delete rows for me. have data in column E and I need the code to look for any cell that has the word "DELETE" in column E. When it finds the word "DELETE", I need it to delete the entire row. For instance if the first instance of this in cell E41120, then it should delete that entire row and look for the next instance to do the same.

I have seen codes out there to delete rows, but I can't get any of it to work for my situation.

View 7 Replies View Related

How To Delete Rows Using VBA Code

Jan 1, 2009

I have data in cells Starting from

B1= Quality, C1= Size, D1= Quantity, E1= Length, F1= Width, G1= Thickness

My data comprises from B2:D2000 ( I am exporting data from SQL Server) so the data is in the horrible conditions , having blanks cells in these three columns . Columns E , F and G are empty.

Can someone help me in cleaning these empty rows in the three columns on the following conditions

delete entire row if column B, C and D contains empty or blank cells or zero
delete entire row if any cells in Column C and D contains text values
delete the entire row if Cells in colums A contains the word Map

the data in the column C is in the following format( Length x Width x Thickness)

Is it possible to get this data in the following form


View 9 Replies View Related

Delete Rows Code

Mar 26, 2009

(excel 2007)

I am using the following piece of code, which I have copied and modified from another workbook in which it worked properly.

Option Explicit
Sub DeleteRows()

'Delete Unneeded Rows
Dim lUsedRangeRows As Long
Dim lRowCounter As Long

With ThisWorkbook.Sheets("Data")
lUsedRangeRows = .UsedRange.Rows.Count

For lRowCounter = lUsedRangeRows To 3 Step -1 'work from the bottom up
If DateValue(.Cells(lRowCounter, 5)) < DateValue(.Range("PayDate")) Then
.Cells(lRowCounter, 5).EntireRow.Delete
End If
Next lRowCounter
End With
End Sub

I receive a "Type Mismatch Error" on the If DateValue line (highlighted red). PayDate is a named range for cell A2 on the same page.

View 9 Replies View Related

VBA Code - Loop Through And Delete Rows

Oct 12, 2013

I use this code to delete some rows, how I would modify it to work in another spreadsheet where it would "Loop" through and delete rows that start with "User:Kellcyna" down to where the rolls start with "Numbers", and delete the rolls that start with "Total cost center" down to where the rolls start with "Numbers".

The data can contain up to 50000 rolls at times.

Sub Finally()
Application.ScreenUpdating = True
[a:a].AutoFilter Field:=1, Criteria1:="="
If [a1] = "" Then [1:1].Delete
ActiveSheet.AutoFilterMode = False
[Code] ........

Here is a sample of the data I need the macro to work on. The rows I need deleted are the rows that are highlighted.

User: Kellcyna STANDARD HOURS BY COST CENTER Date: 09/29/2013 Time: 15:10:04 Page: 10


Order Op Emp Post Work ctr Setup Unit Planned Earned Total Actual Actual Actual Total Total Orde C R

# Date SU Unit Plnd Stds Setup Run Tme Brd Tme Prod Run Time E

104527059 0010 00000000 09/25/2013 HSW01 0.000 0.4 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01

104523849 0010 00000000 09/25/2013 HSW01 0.000 0.2 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01

[Code] ...........

View 5 Replies View Related

Code To Delete All Empty Rows

May 20, 2014

I need a code that I can run that will delete all rows that have nothing in at the bottom of the sheet. What I mean is I will be printing a file and because rows have been deleted that had data in a lot of sheets are being printed and wasted. So what I need is a before close code or something that will actually make the last row with data in the last row!

View 2 Replies View Related

Code To Delete Empty Rows

May 7, 2007

I have a database and at times I have empty rows. I am trying to write code that looks at the database (it may change daily) and deletes the rows that are empty. The code I've written does NOT work:

Sub emptyrows()
Dim emptyrows As Object
Set emptyrows = Cells(65536, 255)
If Rows = "" Then
Selection.Delete Shift:=xlUp
End If
End Sub

View 9 Replies View Related

Code Won't Delete All Blank Rows

Sep 25, 2009

When I tried this code on excel at home it worked, but now i'm at work and it dosen't delete any rows at all!

Here's the ....

View 9 Replies View Related

Fastest Way To Delete Rows In Code

Aug 17, 2007

I have a bit of code that deletes row by row and takes a long time to process. I've seen people suggest deleting by range processes more quickly. How can I modify this to delete by range?

lngLastRow = Sheets(2).Range("A65536").End(xlUp).row
For lngRowCount = lngLastRow To 1 Step -1
If Application.WorksheetFunction. CountIf(Sheets(2).Range("A1:A" & lngRowCount), Sheets(2).Range("A" & lngRowCount)) > 1 Then
Sheets(2).Range("A" & lngRowCount).EntireRow.Delete
End If

View 7 Replies View Related

Delete Duplicate Rows Code ...

Jun 4, 2008

I have trouble in deleting the duplicate rows. I have a code to find the duplicate values

Public Sub Unique_Proj() 'This is the first step which takes out the unique projects from the base data
'Call Work_Assignment
'this selects the unique projects in the sheet
Application. ScreenUpdating = False
Dim A, E, B(), n As Long
With ActiveSheet
A = . Range("g2", .Range("g" & Rows.Count).End(xlUp)).Value
Redim B(1 To UBound(A, 1), 1 To 1)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each E In A
If Not IsEmpty(E) And Not .exists(E) Then
n = n + 1: B(n, 1) = E: .Add E, Nothing
End If
End With
Sheets("Unique Projects").Select
Range("G3:G" & Rows.Count).ClearContents
Range("G3").Resize(n).Value = B

End With
Application.ScreenUpdating = True
End Sub

However, this gives me a list of values in that perticular column only.

What I want is either delete the complete row which is duplicate OR select the entire range of values which are unique (based on the column searched) and paste it in a new sheet. The second option is more better for me.

View 7 Replies View Related

VBA Code To Delete Rows If Specific Value Is Not Present?

Aug 6, 2014

I'm trying to write a macro to update a report including deleting all rows that do not contain a specific value of TP in column K. Below is one of the examples.

[Code] .....

View 3 Replies View Related

Code To Delete Rows Based On Condition

Apr 24, 2009

I have an Excel spreadsheet that is given to me weekly, but I'd like to remove repetitive and empty rows before presenting it to someone. It is set up like this:

Row 1, A1, contains the word Project.
Row 2 is blank
Row 3 is blank
Row 4 is blank
Row 5, A5, contains the word Organization.
Row 6 is blank
Row 7, A7, contains a 6-digit number starting with 3.
Row 8 is blank

(all of the above starts over again (loops) approximately 30 times)
The final row contains the phrase "Grand Total"

*Row 5 is repetitive and is not required. I'd like to delete it.

I have too many empty columns. Getting rid of them (and Row 5) would greatly shorten my spreadsheet.
I am aiming for:


(Repeat until finished)

Grand Total:

View 4 Replies View Related

Code To Delete Rows After Selected Cell

Sep 24, 2011

I've got a sheet that I build from a weekly sheet. I can have 30 to 60 rows, but I always have 10 to 20 rows left over with data in column C, none in A, my last code is "Range("A1").End(xlDown).Offset(1, 0).Select" which takes me to the last cell in column A. Any code that I can then delete say 20 rows after that? I tried one code, but it wanted to delete everything below the cell selected which ran & ran.

"-" are blanks. Range moves the selected cell to A4. I want to delete row selected + 19 more.


View 9 Replies View Related

VBA Code To Delete Duplicate Rows In Workbook

Sep 8, 2013

I have a worksheet populated with data. I have sorted the data and have dupulicate values in column B,C,D up to H.
I have put in a formula to give me a 1 value in column A where there are duplicates. Is there a way to write a VBA code that will Delete all the rows that have a 1 in column A.

View 4 Replies View Related

Compare Data With Specific Code To A List Of Codes

Dec 15, 2011

I got 2 sheets. (1 called "Revenue", the other "101") in the sheet revenue are department codes (variable 100 to 999) they are situated in row "B". the corresponding data in row "D".

this data needs to be transported to the sheet "101"

in the sheet "101" are put the numbers 100 to 999 in row "A"

in row "B" we need the data from the revenue sheet that is corresponding with the department code.

View 1 Replies View Related

Code To Delete Rows - Reset Running Total

Dec 6, 2012

I have a sheet with several thousand rows. I want code that will do this:

- After finding a "D:" in column A (contained in the cell but will be at a different place each row), start a running total of values in column B.
- When finding "F0" in column A...
--if the running total of values for rows that have "D:" equals the value in column B with the "F0", delete the "D:" rows
--if the running total doesn't match, reset the running total and search for the next "D:"
- Repeat for all rows.

View 1 Replies View Related

Code To Delete Rows Based On User Input

Aug 21, 2008

I am using the following piece of code to delete unwanted rows from a worksheet:

Sub DeleteDates()

FinalRow = Cells(65536, 3).End(xlUp).Row
For i = FinalRow To 1 Step -1
If Cells(i, 3).Value Like "*2007*"
Cells(i, 1).EntireRow.Delete
End If

Next i

End Sub

What I am working with is a sheet of about 5000 customer appointments going back to the beginning of 2006. A have peiced together a lot of code to format it exactly as I want and to create a pivot table of what is remaining after the rubbish has been deleted.

However I am finding that I need to keep amending and re-running this bit of code, depending on the date range I want to look at. For example the code above will delete all appointments made in 2007.

My question is: is there a way that I could incorporate an input box, whereby excel asks the user for a start date and an end date and deletes any rows outwith that range?

Some important info: All of the dates are in column C, in the format mmm yyyy

I would only need to narrow down to a month - so for example mar 2008 - jun 2008

View 9 Replies View Related

Speed Up Macro Code To Delete Duplicate Rows

May 2, 2008

I have a VBA Macro that loops through about 100,000 rows in an Excel file and removes rows that have a duplicate cell value. The macro takes about an hour and a half to run. Are there any ways to make the Macro run faster? Any ways in general to make VBA macros run faster?

View 9 Replies View Related

Code Runs When I Hide Rows Or Delete Cells

Aug 2, 2008

So i don't have any events that i know of that should trigger my macros to run. But everytime i delete a cell, or hide rows, it seems all my code/macros just auto run. And i had a few times when i hid the rows, the code would start, and then my excel application would crash.

View 4 Replies View Related

Modify And Enhance Recorded Code - Delete Rows Based On Value

Aug 22, 2012

I searched many sites and used codes which delete Rows based on criteria. In my case those codes works, BUT it took so much time about 30 min since there is about 75 thsd rows, and that solution in not time saver. I recorded code below and it is done in seconds. make it dynamic, VBA, since number of rows is every month larger. Basicly, I need code which delete rows based on Column B, where walue is "R"

Sub DelRowsBasedOnOneCriteria()
' DelRowsBasedOnOneCriteria Macro


View 4 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved