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.
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" <js@someone@hotmail.com> wrote in message
news:e1V5uZDAGHA.1408@TK2MSFTNGP15.phx.gbl...
> 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.
>
>
Thanks Norman...
How to run? I'm new to excel.
"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:Ocm$ueDAGHA.272@tk2msftngp13.phx.gbl...
> 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" <js@someone@hotmail.com> wrote in message
> news:e1V5uZDAGHA.1408@TK2MSFTNGP15.phx.gbl...
>> 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.
>>
>>
>
>
Hi Norman,
the condition is not numeric,
>>.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete?
How to add it?
"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:Ocm$ueDAGHA.272@tk2msftngp13.phx.gbl...
> 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" <js@someone@hotmail.com> wrote in message
> news:e1V5uZDAGHA.1408@TK2MSFTNGP15.phx.gbl...
>> 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.
>>
>>
>
>
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" <js@someone@hotmail.com> wrote in message
news:%23G0S2lDAGHA.2040@TK2MSFTNGP14.phx.gbl...
> Hi Norman,
>
> the condition is not numeric,
>
>>>.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete?
>
> How to add it?
>
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:Ocm$ueDAGHA.272@tk2msftngp13.phx.gbl...
>> 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" <js@someone@hotmail.com> wrote in message
>> news:e1V5uZDAGHA.1408@TK2MSFTNGP15.phx.gbl...
>>> 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.
>>>
>>>
>>
>>
>
>
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
Originally Posted by Norman Jones
Thx
Dave
"The game is afoot Watson"
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@excelforum-nospam.com> wrote in
message news:Desert.Piranha.200b90_1134524702.0846@excelforum-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" <js@someone@hotmail.com> wrote in message
>> news:%23G0S2lDAGHA.2040@TK2MSFTNGP14.phx.gbl...
>> > Hi Norman,
>> >
>> > the condition is not numeric,
>> >
>> >>>.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete?
>> >
>> > How to add it?
>> >
>> >
>> >
>> > "Norman Jones" <normanjones@whereforartthou.com> wrote in message
>> > news:Ocm$ueDAGHA.272@tk2msftngp13.phx.gbl...
>> >> 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" <js@someone@hotmail.com> wrote in message
>> >> news:e1V5uZDAGHA.1408@TK2MSFTNGP15.phx.gbl...
>> >>> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks