Delete Rows based on information in column

Tutorials Win: Microsoft Windows Forum

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
Top

Re: Delete Rows based on information in column by macropod

macropod
Mon Nov 26 23:05:53 PST 2007

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]
-------------------------

"jlclyde" <jlclyde@taylorcorp.com> wrote in message news:1e4b3974-1ddf-4b8b-8beb-03ee65c65202@e4g2000hsg.googlegroups.com...
>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
Top

Re: Delete Rows based on information in column by jlclyde

jlclyde
Tue Nov 27 05:46:13 PST 2007

On Nov 27, 1:05 am, "macropod" <inva...@invalid.invalid> wrote:
> 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]
> -------------------------
>
>
>
> "jlclyde" <jlcl...@taylorcorp.com> wrote in messagenews:1e4b3974-1ddf-4b8b-8beb-03ee65c65202@e4g2000hsg.googlegroups.com...
> >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
Top

Re: Delete Rows based on information in column by macropod

macropod
Tue Nov 27 12:48:34 PST 2007

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]
-------------------------

"jlclyde" <jlclyde@taylorcorp.com> wrote in message news:83bddabd-4865-405d-89ae-3f92eeabe351@w28g2000hsf.googlegroups.com...
> On Nov 27, 1:05 am, "macropod" <inva...@invalid.invalid> wrote:
>> 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]
>> -------------------------
>>
>>
>>
>> "jlclyde" <jlcl...@taylorcorp.com> wrote in messagenews:1e4b3974-1ddf-4b8b-8beb-03ee65c65202@e4g2000hsg.googlegroups.com...
>> >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
Top