Deleting rows with VBA
 

Hello,

I dabble in Word VBA but today I am challenged with an Excel
spreadsheet and find myself hopelessly lost.

Until corrected I will refer to the spreadsheet grid as rows and
columns. I have many rows and many columns. The value in column "P"
is either 1 or 0. I want to delete any row that has a "P" column value
of 0.

This is my attempt at logical thought but it doesn't work. Please
help.

Sub DeleteRows()

For Each oRow in ActiveSheet.Rows
If ActiveSheet.Columns("P").value = 0 Then
oRow.Delete
End If
Next oRow

End Sub

Deleting rows with VBA
 

Hi Greg

I have examples on this page
http://www.rondebruin.nl/delete.htm

If you need more help post back
Check out the Autofilter example first (fast)

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


"Greg http://gregmaxey.mvps.org/word_tips.htm" wrote in message
oups.com...
Hello,

I dabble in Word VBA but today I am challenged with an Excel
spreadsheet and find myself hopelessly lost.

Until corrected I will refer to the spreadsheet grid as rows and
columns. I have many rows and many columns. The value in column "P"
is either 1 or 0. I want to delete any row that has a "P" column value
of 0.

This is my attempt at logical thought but it doesn't work. Please
help.

Sub DeleteRows()

For Each oRow in ActiveSheet.Rows
If ActiveSheet.Columns("P").value = 0 Then
oRow.Delete
End If
Next oRow

End Sub

Deleting rows with VBA
 

Ron,

Perfect. Thank you very much. The Autofilter example was both fast
and simple.

Deleting rows with VBA
 

Greg,

How about something like this?

Sub DeleteRowsWithPequalZero()

Dim TestRow As Range

For Each TestRow In Range("A1:A100")
If Range("P" & TestRow.Row).Value = 0 Then
TestRow.EntireRow.Delete
End If
Next

End Sub

Don't use activesheet.rows, otherwise you'll be testing right down to row
65536!
You could also subsitute a Range Name instead of cell references:

For Each TestRow In Range("MyTestRange")

Hope this helps

Pete





"Greg http://gregmaxey.mvps.org/word_tips" wrote:

Hello,

I dabble in Word VBA but today I am challenged with an Excel
spreadsheet and find myself hopelessly lost.

Until corrected I will refer to the spreadsheet grid as rows and
columns. I have many rows and many columns. The value in column "P"
is either 1 or 0. I want to delete any row that has a "P" column value
of 0.

This is my attempt at logical thought but it doesn't work. Please
help.

Sub DeleteRows()

For Each oRow in ActiveSheet.Rows
If ActiveSheet.Columns("P").value = 0 Then
oRow.Delete
End If
Next oRow

End Sub

Deleting rows with VBA
 

Pete,

Yes, that works well too. Thanks