I'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 Repliesremoving 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 have a large report that I pull each month listing which products we sold last month. There are certain products I don't want included in the report. Until I can have the report re-written, I'm looking for a macro or a function that will find all the rows that contain these product numbers in column M and delete the entire row. The product numbers I need to delete are always the same, but some months one or more of them might not be in the report if they weren't sold.
In Summary:
Find in column M the product numbers:
c1000
316140a
316140
316295a
316295
316311a
316311
316451a
316451
316450a
316450
316452a
316452
Delete those rows containing the product numbers above
create a VBA to delete rows if the cell doesn't have "Other".
The data is in Column C which consist of the below data so I like VBA to delete the rows when the cell doesn't have "Other"..
-Primary
-Business
-Home
-(blank)
-Other
two 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!
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 RelatedIn the code below I find rows containing "$$ #" at the far left and process it.
For all the remaining rows I want to delete the entire row, my macro only deletes some rows. Obviously I am not grasping something about looping throught the rows to delete unwanted data.
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].....
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 )
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
I'm looking to loop through a worksheet with over 1000 columns and would like to delete columns containing a percent sign (%) in the header row which is row 4. Column range to search would begin at column 12.
View 4 Replies View RelatedI an using Excel 2003. In that i am having 2 columns (A&B). I need to take each value from Column A and search that cell value in Column B. If it is not found in Column B then delete the cell data in Column A. Then take the next cell data in Column A. Find operation and delete if not found. Do it for entire rows in column A. Then repeat that for Column B also. So my final result will be the same cell value in Column A and B. I need VBA code.
View 2 Replies View RelatedI an using Excel 2003. In that i am having 2 columns (A&B). I need to take each value from Column A and search that cell value in Column B. If it is found in Column B then delete the cell data in Column A. Then take the next cell data in Column A. Find operation and delete if found. Do it for entire rows in column A. Then repeat that for Column B also. So my final result contains uncommon cell value in Column A and B. I need VBA code.
View 3 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 RelatedBasically i have a column which runs like this:
Company Name:
Name1
Name1
Name1
BLANK
BLANK
BLANK
Name1
BLANK
Name1
Basically, what I am after doing is getting a script to look through that column and delete any rows that do not have "Name1" in Column A.
So far, i've sorted the column so all the blank cells appear at the top, and the "Name1" cells appear at the bottom. Now what I was going to do was use a loop, and I got as far as this before becoming confused .....
Looking for some VBA code that will look for the first instance of a certain value in Column A, (Begins with 9128),then delete all rows above that row. Then I need to look for a value in Column A that begins with 9129, and delete all rows below that. I've attached a sample workbook to illustrate.
View 3 Replies View RelatedMacro code to find several column names in row 1 (which can be anywhere in the work sheet) and delete them e.g. date, month, code, amount etc.......
Excel version 2007
writing a macro to find the max value in each column and delete all the data points that come after it (or preferably: delete all the data points that come 2 rows down after the max, if possible). There will be many columns of data where the max will come at different positions in the column.
Instead of deleting post-max values, it would also be acceptable to just copy values from the beginning to the max to the same column in a new worksheet.
I need to loop through data when it finds a blank it need to delete that row and 15 rows down and loop through whole data set find blank row and delete 15 rows down.
It's been years since I did any VBA, and I forgot. Do I need to use offset to acheive this task?
I get a report each week that has a bunch of unwanted header info. It used to be static and the macro just deleted the first 49 rows. I recently found out that the header format changed and it's been deleting crucial data.
I need something that will find the words "Medical Center" and delete all the rows above it, however many that may be...
I have searched high and low for a more efficient macro than the current loop i have. I have a range that varies in size but always follows the same format. After i reach a certain value output from a formula, "4", I want to delete all lines below that line. So I want to find value "4" then move one row down and delete all lines to line 2004. I have attached the macro that i compiled but it takes a couple of minutes to run.
Sub manual()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
. ScreenUpdating = False
End With
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView.................
I have a report that comes in a txt file. After importing into excel i am left with a bunch of garbage that i dont need. This report is anywhere from 5-15 pages depending on how much product was made that shift. I only need the information off of the 1st page. My question is how can i Find the first occurance of specific text (Site) (will be in column A) and have it select that row and all rows below and delete them.
My biggest issue is the first page can be anywhere from 40-60 rows so I need to find the text (Site) and delete everything below it, which could be anywhere from 500-1500 rows.
I am trying to select all the data in every row below where the first "flat" entry in the worksheet is but it will only go to the bottom of the data in the column where it finds flat. Some data in other columns extends beyond this so it is not working for me. It would work for me to select everything from where it finds flat to the very last row 65536 but I can't figure out how to do that with what I have. I am then deleting all this data.
Cells.Find(What:="flat", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Select
range(Selection, Selection.End(xlDown)).Select
range(Selection, Selection.End(xlDown)).Select
range(Selection, Selection.End(xlDown)).Select
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 am trying to get macro which will find word "Service" in sheet 1 and after that delete that row and below row.
View 1 Replies View RelatedI have a worksheet that is several thousand rows long. It is sectioned into various "topics" (e.g. Revenue, Profit); each topic (there are about 100) has 10 companies. The 10 companies are the same for each topic. For instance, if 2 of the companies are Microsoft and Intel, then there will be a Microsoft row and an Intel row within each topic.
I am trying to write some code to delete every instance of a company (the user indicates the company in a form). So, for instance, if I want to delete Microsoft, the user would enter the stock ticker, MSFT, into the form and press the appropriate command button. When the command button is pressed, the code should find each row that contains the ticker MSFT (the tickers happen to be in column B) and delete it.
I have written the following unsuccessful
Private Sub CommandButton1_Click()
Dim Ticker As String
Ticker = TextBox1.Value
'If IsText(Ticker) Then
Range("$A$5").Select
Cells.Find(What:=Ticker, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate..........
1) Highlight column A, and do a search on the word "Item"
2) Once it finds the word item, move 2 rows above it, and delete all rows above the word "Item"
The word Item at times can be on any row, and its very random, but its always in column, A. I tried doing it with a regular macro, but it seems to remember the row that I used, rather than two rows above the word "Item".
I am trying to create a macro that will delete everything below a certain word.
The code I tried to use is the following. This produced an error:
If Range(Cells(1, 1), Cells(180, 1)). Find(" Interest Expense").Row = 0 Then
iStart = Range(Cells(1, 1), Cells(180, 1)).Find(" NET INCOME").Row + 1
Else
iStart = Range(Cells(1, 1), Cells(180, 1)).Find(" Interest Expense").Row + 1
End If
Rows(iStart & ":" & FinalRow).Delete shift:=xlUp