Automatically delete rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ivor Williams - 02 Mar 2005 16:30 GMT

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.

Reply to this Message

Gord Dibben - 02 Mar 2005 17:26 GMT

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

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

Reply to this Message

Ron de Bruin - 02 Mar 2005 18:40 GMT

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

Signature

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

> Ivor
>
[quoted text clipped - 16 lines]
>>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.

Reply to this Message

Gord Dibben - 02 Mar 2005 20:17 GMT

Thanks for the correction Ron.

Not tested thoroughly. Assumed data start in row 1.

Gord

>Hi Ivor
>
[quoted text clipped - 6 lines]
>See example 1 on this page for a solution
>http://www.rondebruin.nl/delete.htm

Reply to this Message

Ivor Williams - 04 Mar 2005 03:45 GMT

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

> Ivor
>
[quoted text clipped - 19 lines]
>>so
>>would like to automate the process as much as possible.

Reply to this Message

Ron de Bruin - 04 Mar 2005 05:13 GMT

Hi Ivor

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

Signature

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

> 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
[quoted text clipped - 22 lines]
>>>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.

Reply to this Message