MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Delete row based on cells in different columns


Posted by Tim on October 18, 2000 6:17 PM

After I have imported a text file of variable length, I need to delete a row if the cells in col A and col S are blank. Can anyone help ?


Posted by Celia on October 18, 2000 7:16 PM


Tim

Sub DeleteRows()
Dim theRange As Range
Dim lastRow&, firstRow&, x&
Set theRange = ActiveSheet.UsedRange
lastRow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastRow To firstRow Step -1
If Cells(x, 1) = "" And Cells(x, 19) = "" Then
Rows(x).Delete
End If
Next
End Sub

Celia


Posted by Tim on October 20, 2000 11:17 PM


Posted by Tim on October 20, 2000 11:41 PM

Thank you, greatly appreciated !

Please forgive my excitement, but is it possible to extend this to also delete the preceding 10 rows when the condition is met, regardless of their content ?


Posted by Celia on October 21, 2000 7:18 AM


Tim
Yes, it can be done.
I presume what you want is that if columns A & S are blank in row 15 (for example), then you want row 15 and rows 5:14 to be deleted.
If so, what do you want to do if there are less than 10 previous rows? Do you want to delete only the row being checked, or delete the row being checked plus all previous rows?
Celia

Posted by Tim on October 21, 2000 7:26 PM

Celia
The latter would be perfect !
Thanks again,
Tim

Posted by Celia on October 22, 2000 3:49 AM

Tim
I have assumed that the first row in the used range is a header row and it should not be deleted :-

Sub DeleteRows()
Dim theRange As Range
Dim lastRow&, firstRow&, x&
Set theRange = ActiveSheet.UsedRange
lastRow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row + 1
For x = lastRow To firstRow Step -1
If Cells(x, 1) = "" And Cells(x, 19) = "" Then
If x > 10 Then
Rows(x & ":" & x - 10).Delete
x = x - 10
Else
Rows(firstRow & ":" & x).Delete
End If
End If
Next x
End Sub

Celia


Posted by Tim on October 23, 2000 7:15 PM

That is................. WONDERFUL !!!!

Thank you, thank you, thank you !

(I can finally feel my headache subsiding..)

Posted by Brad on November 21, 2000 12:15 PM


Celia, how would I do the same thing except delete a row if the value in a column didn't match a specified value?

Brad