Lop to delete rows
Using Excel 2000. I want to loop through column A of my worksheet and if
the contents are text, I want to delete the row. Tried all sorts of suggestions on the web but can's seem to get Istex working. Any suggestion welcome. Thank, Rob |
Lop to delete rows
Hi
Try this for Column A Read more info on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If Application.WorksheetFunction.IsText(.Value) = True Then .EntireRow.Delete End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Using Excel 2000. I want to loop through column A of my worksheet and if the contents are text, I want to delete the row. Tried all sorts of suggestions on the web but can's seem to get Istex working. Any suggestion welcome. Thank, Rob |
Lop to delete rows
Look also at Specialcells, faster.
Sub DeleteRowsWithText() 'This macro delete all rows with a blank cell in column A 'If there are no blanks or there are too many areas you see a MsgBox Dim CCount As Long On Error Resume Next With Columns("A") ' You can also use a range like this Range("A1:A8000") CCount = .SpecialCells(xlCellTypeConstants, xlTextValues).Areas(1).Cells.Count If CCount = 0 Then MsgBox "There are no blank cells" ElseIf CCount = .Cells.Count Then MsgBox "There are more then 8192 areas" Else .SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete End If End With On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Try this for Column A Read more info on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If Application.WorksheetFunction.IsText(.Value) = True Then .EntireRow.Delete End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Using Excel 2000. I want to loop through column A of my worksheet and if the contents are text, I want to delete the row. Tried all sorts of suggestions on the web but can's seem to get Istex working. Any suggestion welcome. Thank, Rob |
Lop to delete rows
Ron,
I had tried this but all rows are deleted, I turned off setting to manual calc in case that was the issue but alas no. When entering =ISTEXT(A1), the formula correctly returns Tue or False but macro deletes all rows. I'll keep trying to figure it out. Thanks, Rob "Ron de Bruin" wrote in message ... Hi Try this for Column A Read more info on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If Application.WorksheetFunction.IsText(.Value) = True Then .EntireRow.Delete End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Using Excel 2000. I want to loop through column A of my worksheet and if the contents are text, I want to delete the row. Tried all sorts of suggestions on the web but can's seem to get Istex working. Any suggestion welcome. Thank, Rob |
Lop to delete rows
Hi Rob
Strange, can you send me your test file private. I look at it then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Ron, I had tried this but all rows are deleted, I turned off setting to manual calc in case that was the issue but alas no. When entering =ISTEXT(A1), the formula correctly returns Tue or False but macro deletes all rows. I'll keep trying to figure it out. Thanks, Rob "Ron de Bruin" wrote in message ... Hi Try this for Column A Read more info on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If Application.WorksheetFunction.IsText(.Value) = True Then .EntireRow.Delete End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Using Excel 2000. I want to loop through column A of my worksheet and if the contents are text, I want to delete the row. Tried all sorts of suggestions on the web but can's seem to get Istex working. Any suggestion welcome. Thank, Rob |
Lop to delete rows
Hi Robert
Because Column A are for 98 % dates we must use Value2 in this example If Application.WorksheetFunction.IsText(.Value2) = True Then .EntireRow.Delete This one is also working correct Sub DeleteRowsWithText() 'This macro delete all rows with a blank cell in column A 'If there are no blanks or there are too many areas you see a MsgBox Dim CCount As Long On Error Resume Next With Columns("A") ' You can also use a range like this Range("A1:A8000") CCount = .SpecialCells(xlCellTypeConstants, xlTextValues).Areas(1).Cells.Count If CCount = 0 Then MsgBox "There are no blank cells" ElseIf CCount = .Cells.Count Then MsgBox "There are more then 8192 areas" Else .SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete End If End With On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Rob Strange, can you send me your test file private. I look at it then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Ron, I had tried this but all rows are deleted, I turned off setting to manual calc in case that was the issue but alas no. When entering =ISTEXT(A1), the formula correctly returns Tue or False but macro deletes all rows. I'll keep trying to figure it out. Thanks, Rob "Ron de Bruin" wrote in message ... Hi Try this for Column A Read more info on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If Application.WorksheetFunction.IsText(.Value) = True Then .EntireRow.Delete End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Using Excel 2000. I want to loop through column A of my worksheet and if the contents are text, I want to delete the row. Tried all sorts of suggestions on the web but can's seem to get Istex working. Any suggestion welcome. Thank, Rob |
Lop to delete rows
Thanks Ron, I will get the hang of this in due course. Again thanks, Rob
"Ron de Bruin" wrote in message ... Hi Robert Because Column A are for 98 % dates we must use Value2 in this example If Application.WorksheetFunction.IsText(.Value2) = True Then .EntireRow.Delete This one is also working correct Sub DeleteRowsWithText() 'This macro delete all rows with a blank cell in column A 'If there are no blanks or there are too many areas you see a MsgBox Dim CCount As Long On Error Resume Next With Columns("A") ' You can also use a range like this Range("A1:A8000") CCount = .SpecialCells(xlCellTypeConstants, xlTextValues).Areas(1).Cells.Count If CCount = 0 Then MsgBox "There are no blank cells" ElseIf CCount = .Cells.Count Then MsgBox "There are more then 8192 areas" Else .SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete End If End With On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Rob Strange, can you send me your test file private. I look at it then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Ron, I had tried this but all rows are deleted, I turned off setting to manual calc in case that was the issue but alas no. When entering =ISTEXT(A1), the formula correctly returns Tue or False but macro deletes all rows. I'll keep trying to figure it out. Thanks, Rob "Ron de Bruin" wrote in message ... Hi Try this for Column A Read more info on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If Application.WorksheetFunction.IsText(.Value) = True Then .EntireRow.Delete End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Using Excel 2000. I want to loop through column A of my worksheet and if the contents are text, I want to delete the row. Tried all sorts of suggestions on the web but can's seem to get Istex working. Any suggestion welcome. Thank, Rob |
All times are GMT +1. The time now is 11:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com