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
|