| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
Hi,
I have a excel file like this: id name 1 ll1 2 ll2 abc xxxxxxxxxxxxxx 10 ll3 23 ll4 my job is to remove the empty line or the id column is not numeric, I manual did it now. I have lot's row. is it possible to do using VBA? just check id is numeric or empty, then delete? I'm new to excel, please help. Thanks. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
delete rows
Hi JS,
just check id is numeric or empty, then delete? Try: '============= Public Sub Tester() With Columns(1) .SpecialCells(xlCellTypeConstants, xlNumbers). _ EntireRow.Delete .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= --- Regards, Norman "js" wrote in message ... Hi, I have a excel file like this: id name 1 ll1 2 ll2 abc xxxxxxxxxxxxxx 10 ll3 23 ll4 my job is to remove the empty line or the id column is not numeric, I manual did it now. I have lot's row. is it possible to do using VBA? just check id is numeric or empty, then delete? I'm new to excel, please help. Thanks. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
delete rows
Thanks Norman...
How to run? I'm new to excel. "Norman Jones" wrote in message ... Hi JS, just check id is numeric or empty, then delete? Try: '============= Public Sub Tester() With Columns(1) .SpecialCells(xlCellTypeConstants, xlNumbers). _ EntireRow.Delete .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= --- Regards, Norman "js" wrote in message ... Hi, I have a excel file like this: id name 1 ll1 2 ll2 abc xxxxxxxxxxxxxx 10 ll3 23 ll4 my job is to remove the empty line or the id column is not numeric, I manual did it now. I have lot's row. is it possible to do using VBA? just check id is numeric or empty, then delete? I'm new to excel, please help. Thanks. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
delete rows
Hi Norman,
the condition is not numeric, .SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete? How to add it? "Norman Jones" wrote in message ... Hi JS, just check id is numeric or empty, then delete? Try: '============= Public Sub Tester() With Columns(1) .SpecialCells(xlCellTypeConstants, xlNumbers). _ EntireRow.Delete .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= --- Regards, Norman "js" wrote in message ... Hi, I have a excel file like this: id name 1 ll1 2 ll2 abc xxxxxxxxxxxxxx 10 ll3 23 ll4 my job is to remove the empty line or the id column is not numeric, I manual did it now. I have lot's row. is it possible to do using VBA? just check id is numeric or empty, then delete? I'm new to excel, please help. Thanks. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
delete rows
Hi JS,
the condition is not numeric, .SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete? How to add it? In that case, try this revised version: '============= Public Sub TesterA() With Columns(1) .SpecialCells(xlCellTypeConstants, xlTextValues). _ EntireRow.Delete .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= As you are new to macros, you may wish to visit David McRitchie's 'Getting Started With Macros And User Defined Functions' at: http://www.mvps.org/dmcritchie/excel/getstarted.htm You may also wish to look at David's tutorial page: http://www.mvps.org/dmcritchie/excel....htm#tutorials To run the suggested code, try: Copy the code Alt-F11 to open the VBA editor Menus | Insert | Module Paste the code Alt-F11 to return to Excel Select the worksheet of interest Alt-F8 to open the Macro Dialog Select the macro in the dropdown window Run I suggest that you try the code on a copy of your data to verify that the code achieves your intended results. --- Regards, Norman "js" wrote in message ... Hi Norman, the condition is not numeric, .SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete? How to add it? "Norman Jones" wrote in message ... Hi JS, just check id is numeric or empty, then delete? Try: '============= Public Sub Tester() With Columns(1) .SpecialCells(xlCellTypeConstants, xlNumbers). _ EntireRow.Delete .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= --- Regards, Norman "js" wrote in message ... Hi, I have a excel file like this: id name 1 ll1 2 ll2 abc xxxxxxxxxxxxxx 10 ll3 23 ll4 my job is to remove the empty line or the id column is not numeric, I manual did it now. I have lot's row. is it possible to do using VBA? just check id is numeric or empty, then delete? I'm new to excel, please help. Thanks. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
delete rows
H Norman, This code of yours: '============= Public Sub TextAndBlanks() With Columns(1) ..SpecialCells(xlCellTypeConstants, xlTextValues). _ EntireRow.Delete ..SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= Will delete rows with all combinations of text and spaces IE: a 1a 1 a a1 a 1 The other code however for the numbers '============= Public Sub NumbersAndBlanks() With Columns(1) ..SpecialCells(xlCellTypeConstants, xlNumbers). _ EntireRow.Delete ..SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= Will only delete rows if the entire string is numbers or spaces. Not when there is a combination IE: 1a a1 a 1 1 aa Why wouldn't they both do the same thing, one for numbers, one for text? Dave Norman Jones Wrote: Hi JS, the condition is not numeric, .SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete? How to add it? In that case, try this revised version: '============= Public Sub TesterA() With Columns(1) .SpecialCells(xlCellTypeConstants, xlTextValues). _ EntireRow.Delete .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= As you are new to macros, you may wish to visit David McRitchie's 'Getting Started With Macros And User Defined Functions' at: http://www.mvps.org/dmcritchie/excel/getstarted.htm You may also wish to look at David's tutorial page: http://www.mvps.org/dmcritchie/excel....htm#tutorials To run the suggested code, try: Copy the code Alt-F11 to open the VBA editor Menus | Insert | Module Paste the code Alt-F11 to return to Excel Select the worksheet of interest Alt-F8 to open the Macro Dialog Select the macro in the dropdown window Run I suggest that you try the code on a copy of your data to verify that the code achieves your intended results. --- Regards, Norman "js" wrote in message ... Hi Norman, the condition is not numeric, .SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete? How to add it? "Norman Jones" wrote in message ... Hi JS, just check id is numeric or empty, then delete? Try: '============= Public Sub Tester() With Columns(1) .SpecialCells(xlCellTypeConstants, xlNumbers). _ EntireRow.Delete .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= --- Regards, Norman "js" wrote in message ... Hi, I have a excel file like this: id name 1 ll1 2 ll2 abc xxxxxxxxxxxxxx 10 ll3 23 ll4 my job is to remove the empty line or the id column is not numeric, I manual did it now. I have lot's row. is it possible to do using VBA? just check id is numeric or empty, then delete? I'm new to excel, please help. Thanks. -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=493213 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
delete rows
Hi Dave,
The SpecialCells method regards combinations of spaces and alphanumeric characters as text constants. Therefore, as you observe, the code: SpecialCells(xlCellTypeConstants, xlTextValues). _ EntireRow.Delete will delete all alphanumeric strings, including those with embedded spaces. Number constants cannot include spaces, so the code: SpecialCells(xlCellTypeConstants, xlNumbers). _ EntireRow.Delete will ignore any constant containing embedded spaces and only delete numeric constants. In both cases, the code: SpecialCells(xlCellTypeBlanks).EntireRow.Delete relates only to empty (blank) cells. Empty cells are not recognised as either text or number constants: hence the use of this latter code line in both instances. --- Regards, Norman "Desert Piranha" <Desert.Piranha.200b90_1134524702.0846@excelforu m-nospam.com wrote in message news:Desert.Piranha.200b90_1134524702.0846@excelfo rum-nospam.com... H Norman, This code of yours: '============= Public Sub TextAndBlanks() With Columns(1) SpecialCells(xlCellTypeConstants, xlTextValues). _ EntireRow.Delete SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= Will delete rows with all combinations of text and spaces IE: a 1a 1 a a1 a 1 The other code however for the numbers '============= Public Sub NumbersAndBlanks() With Columns(1) SpecialCells(xlCellTypeConstants, xlNumbers). _ EntireRow.Delete SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= Will only delete rows if the entire string is numbers or spaces. Not when there is a combination IE: 1a a1 a 1 1 aa Why wouldn't they both do the same thing, one for numbers, one for text? Dave Norman Jones Wrote: Hi JS, the condition is not numeric, .SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete? How to add it? In that case, try this revised version: '============= Public Sub TesterA() With Columns(1) .SpecialCells(xlCellTypeConstants, xlTextValues). _ EntireRow.Delete .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= As you are new to macros, you may wish to visit David McRitchie's 'Getting Started With Macros And User Defined Functions' at: http://www.mvps.org/dmcritchie/excel/getstarted.htm You may also wish to look at David's tutorial page: http://www.mvps.org/dmcritchie/excel....htm#tutorials To run the suggested code, try: Copy the code Alt-F11 to open the VBA editor Menus | Insert | Module Paste the code Alt-F11 to return to Excel Select the worksheet of interest Alt-F8 to open the Macro Dialog Select the macro in the dropdown window Run I suggest that you try the code on a copy of your data to verify that the code achieves your intended results. --- Regards, Norman "js" wrote in message ... Hi Norman, the condition is not numeric, .SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete? How to add it? "Norman Jones" wrote in message ... Hi JS, just check id is numeric or empty, then delete? Try: '============= Public Sub Tester() With Columns(1) .SpecialCells(xlCellTypeConstants, xlNumbers). _ EntireRow.Delete .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With End Sub '<<============= --- Regards, Norman "js" wrote in message ... Hi, I have a excel file like this: id name 1 ll1 2 ll2 abc xxxxxxxxxxxxxx 10 ll3 23 ll4 my job is to remove the empty line or the id column is not numeric, I manual did it now. I have lot's row. is it possible to do using VBA? just check id is numeric or empty, then delete? I'm new to excel, please help. Thanks. -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=493213 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
| Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
| Delete rows with numeric values, leave rows with text | Excel Programming | |||
| How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
| Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming | |||
Hybrid Mode
