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
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 Relateda code that will delete blank rows between data, below is sample of my data, the header row is 8
From this ....
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.
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.
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.
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.
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 RelatedI'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):
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 .............
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
data
data
data
empty row
custom table
data
data
data
data
empty row
custom table
data
data
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.
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)
C2=78x36x4
C3=78.5x36.5x4.5
C4=7x4x3
C5=72x36x0.5
C6=72x36x19mm
Is it possible to get this data in the following form
E2=78,F2=36,G2=4
E3=78.5,F3=36.5,G3=4.5
E4=7,F4=4,G4=3
E5=72,F5=36,F6=0.5
E6=72,F6=36,G6=19mm
(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.
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:="="
[a2:a65536].SpecialCells(xlVisible).EntireRow.Delete
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
Comments:
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] ...........
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 RelatedI 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
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 ....
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
Next
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
Sheets("Projects").Select
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
Next
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.
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] .....
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:
Project
301111
Project
301112
Project
301110
(Repeat until finished)
Grand Total:
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.
-|A|B|C|D
1|Y|2|9|J|
2|R|1|8|N|
3|G|4|7|N|
4|-|-|L|-|
5|-|-|L|-|
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.
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.
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.
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
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 RelatedSo 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 RelatedI 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"
Code:
Sub DelRowsBasedOnOneCriteria()
'
' DelRowsBasedOnOneCriteria Macro
[Code]....