Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find/delete row when cell has less than 16 alphanumeric characters
Hello,
How can I find a cell in column "E" that has less than 16 characters and then delete that row? Also, is there also a way to find duplicate cells in a column and delete those rows containing those duplicate cells? Ex: three rows of data are duplicates so i want to delete 2 of those rows. Thanks in advance!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find/delete row when cell has less than 16 alphanumeric characters
To delete rows with less than 16 characters use the below macro. For removing
duplicates Select range and from Advanced Filter option check unique records only. You can copy that to another sheet. For running the macro Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run MyMacro() Sub MyMacro() lngRow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row For lngTemp = lngRow To 1 Step -1 If Len(Trim(Range("E" & lngTemp))) < 16 Then Rows(lngTemp).Delete End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "Peruanos72" wrote: Hello, How can I find a cell in column "E" that has less than 16 characters and then delete that row? Also, is there also a way to find duplicate cells in a column and delete those rows containing those duplicate cells? Ex: three rows of data are duplicates so i want to delete 2 of those rows. Thanks in advance!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find/delete row when cell has less than 16 alphanumeric charac
Thanks Jacob.
"Jacob Skaria" wrote: To delete rows with less than 16 characters use the below macro. For removing duplicates Select range and from Advanced Filter option check unique records only. You can copy that to another sheet. For running the macro Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run MyMacro() Sub MyMacro() lngRow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row For lngTemp = lngRow To 1 Step -1 If Len(Trim(Range("E" & lngTemp))) < 16 Then Rows(lngTemp).Delete End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "Peruanos72" wrote: Hello, How can I find a cell in column "E" that has less than 16 characters and then delete that row? Also, is there also a way to find duplicate cells in a column and delete those rows containing those duplicate cells? Ex: three rows of data are duplicates so i want to delete 2 of those rows. Thanks in advance!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File names appended with ~ plus alphanumeric characters | Excel Discussion (Misc queries) | |||
Deleting non-alphanumeric characters | Excel Programming | |||
Identify non-alphanumeric characters within a cell in Excel | Excel Programming | |||
format cell to place colon btw every 2 alphanumeric characters | Excel Worksheet Functions | |||
Search for non alphanumeric characters | Excel Programming |