Delete & Merge Columns,Delete Rows With Filter, Etc

Jul 15, 2009

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 )

View 3 Replies


ADVERTISEMENT

Delete The Columns First Then Rows Will Not Delete

May 22, 2008

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

Filter Then Delete Unwanted Rows

Oct 8, 2007

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 delete.how to improve this code to accomodate this situation?

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

View 9 Replies View Related

Filter And Delete Any Rows Where There Is A Value In A Column

May 5, 2006

I have written some code that will allow me to filter and delete any rows where there is a value in a column ( column J in this instance ) more than 2500. The problem is that it only wolrs when i press F8 and step through the code, and not when i press the commnd button and run it.

Sheets("CAIZOLY9").Select
Range("A1").Select
Do Until ActiveCell = "Payment Amount"
ActiveCell.Offset(0, 1).Select
ActiveCell.Select
Loop

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
If ActiveCell > 2499.99 Then
Selection.EntireRow.Delete
Else
End If
Loop
Range("A1").Select
End Sub

View 7 Replies View Related

AutoFilter VBA - Filter And Delete Unwanted Rows

Oct 25, 2012

I'm trying to filter and delete unwanted rows as I need row where dates is within a specified range.

How can I do this using AutoFilter? or are there any other alternatives?

View 2 Replies View Related

Macro To Filter And Delete Row Not Deleting Visible Filtered Rows

Jun 6, 2013

I'm working through a filter macro to delete unecessary rows of data from my dataset.

- I have a Dynamic Range for my dataset called "CanadaData"
- I'm trying to delete rows from the 5th column of my dataset for cells containing "DIRECTSHIP"

The macro filters the range fine, but when if comes to deleting the row, the macro stops.

Sub CanadaWarehouseFilter()
x = Range("E" & Rows.Count).End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("E22:E" & x), "DIRECTSHIP") > 0 Then

With Range("CanadaData")

[Code] ......

View 2 Replies View Related

Delete Visible Rows After Auto-Filter & Exclude Headings

Sep 5, 2006

I am asking if it is possible to delete filtered rows? With code.
What I mean is after setting a filter, then deleting all shown rows except row 1, (Titles)
I did a search but nobody seams to have asked this yet, so recorded it, but that did not seam to enlighten me much either.
Or is this the wrong approach, should I delete using a loop, using the filtered criteria as to say delete row, or move on to the next row?, this would be far more time consuming as when all can be deleted at once if possible of corse.

View 4 Replies View Related

Delete Rows With Columns Not Containing (MC)

Mar 29, 2014

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

View 4 Replies View Related

Delete Columns, Not Rows

Jan 12, 2007

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.

View 8 Replies View Related

Delete Rows, But Not Across All Columns

Mar 4, 2008

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.

View 4 Replies View Related

Moving Data From Rows To Columns And Delete Repeated Rows

Apr 16, 2014

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

View 5 Replies View Related

Delete Multiple Columns By Column Name No Criteria Need, Just Delete Them

Jun 24, 2009

I would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.

And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth

View 4 Replies View Related

Delete All Rows That Have Blanks In Both Columns?

Aug 23, 2012

I am trying to delete all the rows that have blanks in both columns "D" and "F".

VB:
With Sheets("2012")
For k = 6 To 1000
If (.Range("D:" & k) = "" & .Range("F:" & k) = "") Then

[Code]....

View 4 Replies View Related

Delete Rows/columns Across All Worksheets

Jun 24, 2009

I have a single workbook with 55 worksheets. I need to delete rows #1-14, then delete columns A & B on all worksheets. The macro below seems to be running in a loop which appears to delete rows 1-14 and columns A & B until there is no more data left on the active sheet only. It does not run on any of the other sheets - even if I select all sheets before running the macro.

View 4 Replies View Related

Delete Rows With Strings In Only One Of Two Columns

May 10, 2012

I have two columns. In the first column, there is in every cell some text. In the second column, there are some blank cells. It looks like this

textCOL1 textCOL2
text1COL1
text2COL1 text2COL2
text3COL1 text3COL2
text4COL1
text5COL1
text6COL1 text6COL2

How do I remove the rows which have text only in the first column?

The output should be:

textCOL1 textCOL2
text2COL1 text2COL2
text3COL1 text3COL2
text6COL1 text6COL2

View 4 Replies View Related

Delete Rows By Criteria In 4 Columns

Jul 14, 2004

I have a large spreadsheet that is 65000 rows and colomns A thru W.
I need a macro to delete rows that if Date and Account and Type are
equal and the net of Quantity is zero delete those rows.

Col A = Date
Col C = Account
Col D = Type
Col J = Quantity

What the macro would do is if an account had 250 rows of activity on
say 7/7/2004 (Date) and same type of account and the net of all
Quantity is equal to zero delete those rows.

View 9 Replies View Related

Delete Empty Rows & Columns

Nov 17, 2006

I would like to delete all unwanted rows and colomns. I know this can be done but Old Age is catching the grey matter

I want to delete all except the following ranges, Range("A1:H27").Select

View 4 Replies View Related

Delete Rows Based On Two Columns

Dec 22, 2006

I have a spreadsheet that in column A has either the letters "C", "LP", or is blank.

In Column L it has either numbers 1 through 100, or the letter "D".

What I need it to do is to check and see if the data in column L is "D" and in the same row column A is blank, then delete that row.

Also, if the data in Column L is "D" and the Data in column A is either "C", or "LP", then I need it to change the "D" to "1".

It might be easier to do it separately because once you run the part that deletes the rows that don’t have a “C” or “LP” then I could probably just do one that says if column L = “D” then change to “1”.

Lets see if I can simplify this for you.

If Column L = “D” and Column A = BLANK then Delete Row
(this would delete all “D’s” that don’t have the “C” or “LP”)

THEN

If Column L = “D” then change to “1”

View 9 Replies View Related

Delete Rows Based On Data From Two Columns?

Sep 21, 2012

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!

View 6 Replies View Related

How To Delete Multiple Rows But That Match In Columns

May 22, 2014

ertret.jpg

I am trying to delete rows that contain the same addresses BASED ON COLUMNS.

Do you see how in columns C and D they all match EXCEPT for row 4? How do I delete allllll the rows that when c and d match.

I want to keep the ones that DO NOT MATCH.

P.S I HAVE 4000 ROWS I WOULD HAVE TO GO THROUGH

View 1 Replies View Related

Delete Duplicate Rows Based Only 2 Columns.

Feb 18, 2010

I need to filter the following file.

I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.

The end result should look similar to columns F,G and H!

View 2 Replies View Related

Mass Delete Certain Rows / Columns Each Time?

Sep 19, 2013

Is there a formula I can use, where I can delete specific rows/columns each time? I have to download a large set of data each week and each time I have to spend hours deleting the same rows and columns. For example, if I have to delete Rows 1,2,3,4,5 and Columns J,I,H,G every time, how do I automate this?

View 10 Replies View Related

Delete Blank Columns And Rows In A Different Sheet?

May 27, 2014

I have command button in Sheet 3 (from where I will be running the macro) and I need a macro which will delete all the blank columns and rows in the data containing in Sheet 1.

View 2 Replies View Related

Delete Columns Depending On Number Of Rows

Jun 5, 2014

I have a workbook, with a worksheet "Summary". In this worksheet "Summary" I would like to delete all columns that have less than 100 rows.

Please see the attached file : Summary.xlsx‎

View 3 Replies View Related

Delete Rows Based On Data In Two Columns

Mar 18, 2014

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 Related

Find The Last Row Without Data In Columns E And F And Delete The 10 Rows Below Them

Mar 14, 2008

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

Delete Columns Which Cross Merged Rows

Aug 12, 2009

I am trying to use a macro to delete 2 columns from a spreadsheet using a macro.

This sounds easy, but due to the fact the columns needing to be deleted ‘cross’ merged rows the macro then deletes all the columns based on the merged cells.

In the attached spreadsheet (ColDel) if you manually highlight columns D and E and then use Edit/Delete this will delete columns D and E correctly.

View 3 Replies View Related

Delete Entire Rows With Duplicates In Two Columns Only

Apr 20, 2012

I need excel to delete entire rows if there are duplicates in two columns combined.

So, if I have this sample data (in reality I have a lot more columns)

1 Tr. ID Last Name First Name Sex
2 RBM_A Miller Johnny M
3 RBM_A Bayron Josephine F
4 RBM_A Jackson Reynaldo M

[Code] ........

I want excel to delete only row 9, not 11, 13 or 14.

So far, if I look for duplicates in both columns it marks duplicates in either one. Also if I delete the duplicates, it deletes them only in those two columns, leaving me with distorted data (in this example, row 14 would have no last or first name, but only the values "RBM_C" in column B and "F" in column E.

View 2 Replies View Related

Delete Duplicate Rows Based On All Columns

Jan 7, 2010

How do I delete duplicate rows in a sheet using a macro. When I say duplicate row, it is not based on a particular column but all the columns, so it is a true duplicate record.

View 9 Replies View Related

Delete Empty Columns And Rows Up To Used Cells

May 13, 2006

I am looking for something that will first delete all empty columns up until it runs into data, then delete all rows that are blank in Column A until it runs into data.
In other words, I recieve different spreadsheets that start their data in different locations and I would like to have them always start in cell A1.

View 4 Replies View Related







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