Need to remove cells with variable data

[cthomas]

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

 

[Guest]

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

 

[Bernie Deitrick]

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

 

[cthomas]

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

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!