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


ADVERTISEMENT

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

Delete Rows Based On Duplicates In X Columns

May 14, 2006

I've been looking through various forums looking for a macro for a particular task, but I have not found something that works yet. Maybe someone could help me out? It's pretty straightforward situation.

I need a macro that removes an entire row when: information in column B and column C are both found in other rows ( duplicates).

I'm dealing with a worksheet with store record information ... Store Name (column B), Street Name (column C), State/Province, etc. I import new store records all the time and many are invalid because they share the same name and street address and therefore need to be removed. The ones that I’ve played with unfortunately end up deleting the cells only and not the entire row.

It's a tedious process to do manually remove entire rows (for duplicate column B&C) when dealing with thousands of rows and I would very much appreciate any assistance or insight into how simplify the process with a macro. I've attached a sample of what I'm working with.

View 9 Replies View Related

Delete Duplicates Rows Based On 2 Columns

Dec 6, 2006

I want to delete the current row if the data on row A and Row B match. Its hard to explain so I posted an example.

View 5 Replies View Related

Delete Columns / Rows By Partial Match

Feb 19, 2008

i would like to find code for a macro(s) to delete selected columns and delete row by partial string..

due to my novice excel skills i would prefer in two separate macros so i can trigger from another macro if poss..

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

Delete Rows And Columns Entirely From Worksheet So It Shows No Cells

May 9, 2014

Please see this attached image: HowTo Do This.PNG

I have this worksheet that I did not create and notice it has entire rows and columns completely gone / deleted. For example, the only columns it has ranges from A to AI and then no more columns exist in the worksheet. For the rows, it has rows 1 through 77 but then it shows a gray background.

How did they do this? What is this called? How can I do this to Worksheets?

View 4 Replies View Related







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