Delete Rows based on information in column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jlclyde - 26 Nov 2007 22:10 GMT

I had asked for help on another Group, I think i am in the right one
now. I have copied info from one workbook to another. Now that it is
here, I need to delete the entire row if the cells in columns J, M, S
and V are empty. All fo the cells need to be empty inorder for the
row to be deleted.

Thanks in advance,
Jay

Reply to this Message

macropod - 27 Nov 2007 07:05 GMT

Hi Jay,

Try this:
Sub DeleteRows()
Dim i As Long
With ActiveSheet
' Loop through the rows
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
' Test cells in columns J, M, S &V
If .Cells(i, 10).Value = "" And .Cells(i, 13).Value = "" And .Cells(i, 19).Value = "" _
And .Cells(i, 22).Value = "" Then .Cells(i, 1).EntireRow.Delete
Next i
End With
End Sub

I suggest that next time you ask in microsoft.public.excel.programming.

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

>I had asked for help on another Group, I think i am in the right one
> now. I have copied info from one workbook to another. Now that it is
[quoted text clipped - 4 lines]
> Thanks in advance,
> Jay

Reply to this Message

jlclyde - 27 Nov 2007 13:46 GMT

> Hi Jay,
>
[quoted text clipped - 29 lines]
>
> - Show quoted text -

Using this code, it deletes every other line. I need it to delete
every line that does not have JMS and V filled.
Thanks,
Jay

Dim i As Long
With ActiveSheet
' Loop through the rows
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
' Test cells in columns J, M, S &V
If .Cells(i, 10).Value = "" And .Cells(i, 13).Value = ""
And .Cells(i, 19).Value = "" _
And .Cells(i, 22).Value = "" Then .Cells(i,
1).EntireRow.Delete
Next i
End With

Reply to this Message

macropod - 27 Nov 2007 20:48 GMT

Hi Jay,

I think I know what the problem was - it should work better if you change:
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
to
For i = .Cells.SpecialCells(xlCellTypeLastCell).Row to 1 Step -1

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

>> Hi Jay,
>>
[quoted text clipped - 46 lines]
> Next i
> End With

Reply to this Message

Rate this thread: