Macro to delete rows
I need a macro to search through a sheet of approximately 5,000 rows and
delete any row where the length of the string in column A is greater than 5. I'm using Excel 2003. Can anyone help? |
Macro to delete rows
This should work:
Sub delSixUp() Dim lastRow As Long, i As Long lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For i = lastRow To 2 Step -1 If Len(Trim(ActiveSheet.Cells(i, 1).Value)) 5 Then Cells(i, 1).EntireRow.Delete End If Next End Sub "Ken G" wrote in message ... I need a macro to search through a sheet of approximately 5,000 rows and delete any row where the length of the string in column A is greater than 5. I'm using Excel 2003. Can anyone help? |
Macro to delete rows
Try
Sub MyMacro() Dim lngRow As Long For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1 If Len(Range("A" & lngRow)) 5 Then Rows(lngRow).Delete End If Next End Sub -- Jacob "Ken G" wrote: I need a macro to search through a sheet of approximately 5,000 rows and delete any row where the length of the string in column A is greater than 5. I'm using Excel 2003. Can anyone help? |
Macro to delete rows
Thanks Jacob. Perfect.
"Jacob Skaria" wrote: Try Sub MyMacro() Dim lngRow As Long For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1 If Len(Range("A" & lngRow)) 5 Then Rows(lngRow).Delete End If Next End Sub -- Jacob "Ken G" wrote: I need a macro to search through a sheet of approximately 5,000 rows and delete any row where the length of the string in column A is greater than 5. I'm using Excel 2003. Can anyone help? |
Macro to delete rows
Thanks also. This works just as well as the previous suggestion.
Problem solved! "JLGWhiz" wrote: This should work: Sub delSixUp() Dim lastRow As Long, i As Long lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For i = lastRow To 2 Step -1 If Len(Trim(ActiveSheet.Cells(i, 1).Value)) 5 Then Cells(i, 1).EntireRow.Delete End If Next End Sub "Ken G" wrote in message ... I need a macro to search through a sheet of approximately 5,000 rows and delete any row where the length of the string in column A is greater than 5. I'm using Excel 2003. Can anyone help? . |
All times are GMT +1. The time now is 06:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com