Delete Rows based on information in column

[jlclyde]

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

 

[macropod]

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

 

[jlclyde]

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
--
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
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- Hide quoted text -

- 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

 

[macropod]

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
--
macropod
[MVP - Microsoft Word]
-------------------------

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
--
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
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- Hide quoted text -

- 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