Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
Delete Rows if any cell in Column H is blank but do not Delete Fir | Excel Programming | |||
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows | Excel Programming | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |