Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if cell in column "E" has 1 or more alpha characters
I'm trying to delete rows of data after row 4 if the cell in column "E" has
any alpha characters in it at all. If the cell in column "E" is all numeric then no action is taken on that row. Thoughts?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if cell in column "E" has 1 or more alpha character
This is probably OK with the OP but it does delete rows where the corr. col E
cell contains a string with only numbers. For example, it deletes a row with '5 in the corresponding col. E cell. -- Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach In Excel 2007 double-click to format may not work; right click and select from the menu "Rick Rothstein" wrote: Assuming you are running this code in the ActiveSheet (so that the Range is referenced correctly), then this statement should do what you want... Range("E5:E" & Rows.Count).SpecialCells(xlCellTypeConstants, _ xlTextValues).EntireRow.Delete You can put it into a macro or execute it from the Immediate window. -- Rick (MVP - Excel) "Peruanos72" wrote in message ... I'm trying to delete rows of data after row 4 if the cell in column "E" has any alpha characters in it at all. If the cell in column "E" is all numeric then no action is taken on that row. Thoughts?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if cell in column "E" has 1 or more alpha character
Good point! I had forgotten about that.
-- Rick (MVP - Excel) "Tushar Mehta" wrote in message ... This is probably OK with the OP but it does delete rows where the corr. col E cell contains a string with only numbers. For example, it deletes a row with '5 in the corresponding col. E cell. -- Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach In Excel 2007 double-click to format may not work; right click and select from the menu "Rick Rothstein" wrote: Assuming you are running this code in the ActiveSheet (so that the Range is referenced correctly), then this statement should do what you want... Range("E5:E" & Rows.Count).SpecialCells(xlCellTypeConstants, _ xlTextValues).EntireRow.Delete You can put it into a macro or execute it from the Immediate window. -- Rick (MVP - Excel) "Peruanos72" wrote in message ... I'm trying to delete rows of data after row 4 if the cell in column "E" has any alpha characters in it at all. If the cell in column "E" is all numeric then no action is taken on that row. Thoughts?? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if cell in column "E" has 1 or more alpha character
This worked beautifully. Thx!!!
"Per Jessen" wrote: Try this: Sub DeleteNonNumericRows() LastRow = Range("E" & Rows.Count).End(xlUp).Row For r = LastRow To 4 Step -1 If Not IsNumeric(Cells(r, "E")) Then Rows(r).Delete End If Next End Sub Hopes this helps. --- Per "Peruanos72" skrev i meddelelsen ... I'm trying to delete rows of data after row 4 if the cell in column "E" has any alpha characters in it at all. If the cell in column "E" is all numeric then no action is taken on that row. Thoughts?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete row if cell in column "E" has 1 or more alpha characters | Excel Programming | |||
Delete row if cell in column "E" has 1 or more alpha characters | Excel Programming | |||
Delete row if cell in column "E" has 1 or more alpha characters | Excel Programming | |||
Find the word "total" in a cell, delete row or column | Excel Programming | |||
Find the word "total" in a cell, delete row or column | Excel Programming |