Automatically delete rows

I have a spreadsheet that was created in and exported from QuickBooks.
Before manipulating data in the spreadsheet, I want to delete all rows that
contain anything in column C. What function or code could I use to do this?
Could it be done using a macro? I have to do this many times in a month, so
would like to automate the process as much as possible.

Ivor

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "C").Value <> "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord Dibben Excel MVP

On Wed, 02 Mar 2005 16:30:38 GMT, "Ivor Williams" <[email protected]>
wrote:

>I have a spreadsheet that was created in and exported from QuickBooks.
>Before manipulating data in the spreadsheet, I want to delete all rows that
>contain anything in column C. What function or code could I use to do this?
>Could it be done using a macro? I have to do this many times in a month, so
>would like to automate the process as much as possible.
>

Hi Ivor

Warning :

If your data start in row 3 for example Gord's example will not loop
through the last two rows of your data.
If your data start in row 1 then there is no problem

See example 1 on this page for a solution
http://www.rondebruin.nl/delete.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Gord Dibben" <gorddibbATshawDOTca> wrote in message news:[email protected]...
> Ivor
>
> Sub delete_rows()
> Dim RowNdx As Long
> Dim LastRow As Long
> LastRow = ActiveSheet.UsedRange.Rows.Count
> For RowNdx = LastRow To 1 Step -1
> If Cells(RowNdx, "C").Value <> "" Then
> Rows(RowNdx).Delete
> End If
> Next RowNdx
> End Sub
>
>
> Gord Dibben Excel MVP
>
> On Wed, 02 Mar 2005 16:30:38 GMT, "Ivor Williams" <[email protected]>
> wrote:
>
>>I have a spreadsheet that was created in and exported from QuickBooks.
>>Before manipulating data in the spreadsheet, I want to delete all rows that
>>contain anything in column C. What function or code could I use to do this?
>>Could it be done using a macro? I have to do this many times in a month, so
>>would like to automate the process as much as possible.
>>
>

Thanks for the correction Ron.

Not tested thoroughly. Assumed data start in row 1.


Gord

On Wed, 2 Mar 2005 19:40:20 +0100, "Ron de Bruin" <[email protected]>
wrote:

>Hi Ivor
>
>Warning :
>
>If your data start in row 3 for example Gord's example will not loop
>through the last two rows of your data.
>If your data start in row 1 then there is no problem
>
>See example 1 on this page for a solution
>http://www.rondebruin.nl/delete.htm

Thank you for your response, Gord and Ron.
I can see how the code will work, but there is one small problem. I'm very
familiar with Access and how an event will cause the code to run. How is
this accomplished in Excel?

Ivor

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> Ivor
>
> Sub delete_rows()
> Dim RowNdx As Long
> Dim LastRow As Long
> LastRow = ActiveSheet.UsedRange.Rows.Count
> For RowNdx = LastRow To 1 Step -1
> If Cells(RowNdx, "C").Value <> "" Then
> Rows(RowNdx).Delete
> End If
> Next RowNdx
> End Sub
>
>
> Gord Dibben Excel MVP
>
> On Wed, 02 Mar 2005 16:30:38 GMT, "Ivor Williams" <[email protected]>
> wrote:
>
>>I have a spreadsheet that was created in and exported from QuickBooks.
>>Before manipulating data in the spreadsheet, I want to delete all rows
>>that
>>contain anything in column C. What function or code could I use to do
>>this?
>>Could it be done using a macro? I have to do this many times in a month,
>>so
>>would like to automate the process as much as possible.
>>
>

Hi Ivor

See Chip Pearson's site
http://www.cpearson.com/excel/events.htm




--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ivor Williams" <[email protected]> wrote in message news:QRQVd.569954$6l.513682@pd7tw2no...
> Thank you for your response, Gord and Ron.
> I can see how the code will work, but there is one small problem. I'm very familiar with Access and how an event will cause the
> code to run. How is this accomplished in Excel?
>
> Ivor
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message news:[email protected]...
>> Ivor
>>
>> Sub delete_rows()
>> Dim RowNdx As Long
>> Dim LastRow As Long
>> LastRow = ActiveSheet.UsedRange.Rows.Count
>> For RowNdx = LastRow To 1 Step -1
>> If Cells(RowNdx, "C").Value <> "" Then
>> Rows(RowNdx).Delete
>> End If
>> Next RowNdx
>> End Sub
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Wed, 02 Mar 2005 16:30:38 GMT, "Ivor Williams" <[email protected]>
>> wrote:
>>
>>>I have a spreadsheet that was created in and exported from QuickBooks.
>>>Before manipulating data in the spreadsheet, I want to delete all rows that
>>>contain anything in column C. What function or code could I use to do this?
>>>Could it be done using a macro? I have to do this many times in a month, so
>>>would like to automate the process as much as possible.
>>>
>>
>
>