Need to remove cells with variable data
 

Hi All!
I have a worksheet containing a column for phone numbers. However,
some of the numbers are missing area codes and are listed in 7 digit
format(1234567) How do I remove the rows that contain the cells in the
phone number column that only contain 7 numeric characters. And
sometimes the imported file will have dashes or paranthesis. I can
remove blank rows, or just blank cells, or cells with specific
characters, but how do you remove the data when each digit can be
different? I was hoping the good ol' "*" key would work, but it only
took out cells that actually contain the "*" character. The only
constant is the number of characters, 7. And be gentle, this is only
my third day working with macros.

thanks!
Cheney

Need to remove cells with variable data
 

Assuming your 7 digit numbers are true numbers, enter in a helper column:

=(LEN(A1)=7)*(ISNUMBER(A1))

and copy down. Switch on autofilter and remove rows with value 1
--
Gary''s Student - gsnu200731


" wrote:

Hi All!
I have a worksheet containing a column for phone numbers. However,
some of the numbers are missing area codes and are listed in 7 digit
format(1234567) How do I remove the rows that contain the cells in the
phone number column that only contain 7 numeric characters. And
sometimes the imported file will have dashes or paranthesis. I can
remove blank rows, or just blank cells, or cells with specific
characters, but how do you remove the data when each digit can be
different? I was hoping the good ol' "*" key would work, but it only
took out cells that actually contain the "*" character. The only
constant is the number of characters, 7. And be gentle, this is only
my third day working with macros.

thanks!
Cheney

Need to remove cells with variable data
 

Cheney,

Select a cell in the column with the numbers, and run the macro below. This assumes that your data
is contiguous....

HTH,
Bernie
MS Excel MVP

Sub CheneyDelete7()
Dim myR As Range
Set myR = Intersect(ActiveCell.EntireColumn, ActiveCell.CurrentRegion)
myR.Offset(0, 1).EntireColumn.Insert
myR(1, 2).Value = "Length"
myR(2, 2).Resize(myR.Rows.Count - 1, 1).FormulaR1C1 = "=LEN(RC[-1])"
myR.Offset(0, 1).AutoFilter Field:=1, Criteria1:="7"
myR.Offset(1, 1).Resize(myR.Rows.Count - 1, 1). _
SpecialCells(xlCellTypeVisible).EntireRow.Delete
myR.Offset(0, 1).EntireColumn.Delete
End Sub




wrote in message ups.com...
Hi All!
I have a worksheet containing a column for phone numbers. However,
some of the numbers are missing area codes and are listed in 7 digit
format(1234567) How do I remove the rows that contain the cells in the
phone number column that only contain 7 numeric characters. And
sometimes the imported file will have dashes or paranthesis. I can
remove blank rows, or just blank cells, or cells with specific
characters, but how do you remove the data when each digit can be
different? I was hoping the good ol' "*" key would work, but it only
took out cells that actually contain the "*" character. The only
constant is the number of characters, 7. And be gentle, this is only
my third day working with macros.

thanks!
Cheney

Need to remove cells with variable data
 

On Jun 20, 2:34 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Cheney,

Select a cell in the column with the numbers, and run the macro below. This assumes that your data
is contiguous....

HTH,
Bernie
MS Excel MVP

Sub CheneyDelete7()
Dim myR As Range
Set myR = Intersect(ActiveCell.EntireColumn, ActiveCell.CurrentRegion)
myR.Offset(0, 1).EntireColumn.Insert
myR(1, 2).Value = "Length"
myR(2, 2).Resize(myR.Rows.Count - 1, 1).FormulaR1C1 = "=LEN(RC[-1])"
myR.Offset(0, 1).AutoFilter Field:=1, Criteria1:="7"
myR.Offset(1, 1).Resize(myR.Rows.Count - 1, 1). _
SpecialCells(xlCellTypeVisible).EntireRow.Delete
myR.Offset(0, 1).EntireColumn.Delete
End Sub



wrote in oglegroups.com...
Hi All!
I have a worksheet containing a column for phone numbers. However,
some of the numbers are missing area codes and are listed in 7 digit
format(1234567) How do I remove the rows that contain the cells in the
phone number column that only contain 7 numeric characters. And
sometimes the imported file will have dashes or paranthesis. I can
remove blank rows, or just blank cells, or cells with specific
characters, but how do you remove the data when each digit can be
different? I was hoping the good ol' "*" key would work, but it only
took out cells that actually contain the "*" character. The only
constant is the number of characters, 7. And be gentle, this is only
my third day working with macros.

thanks!
Cheney- Hide quoted text -

- Show quoted text -

You guys Rock! I entered in your entire macro and it worked like a
charm! thank you soooooo much!