|
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. |
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. |
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. |
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. |
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. |
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 |
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 |
delete rows
Thanks Norman Jones...
"Norman Jones" wrote in message Hi Dave, |
delete rows
how to add logic to it so it won't delte rows before 4? Thanks.
"Norman Jones" wrote in message ... 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 |
delete rows
Hi JS,
Try: '============= Public Sub TesterA() Dim rng As Range Dim LRow As Long LRow = Cells(Rows.Count, "A").Row Set rng = Range("A4:A" & LRow) With rng On Error Resume Next .SpecialCells(xlCellTypeConstants, xlTextValues). _ EntireRow.Delete .SpecialCells(xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 End With End Sub '<<============= --- Regards, Norman "js" wrote in message ... how to add logic to it so it won't delte rows before 4? Thanks. "Norman Jones" wrote in message ... 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 |
delete rows
Thanks Norman Jones...
"Norman Jones" wrote in message : .... |
| All times are GMT +1. The time now is 05:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com