delete rows using VB
 

I know there's a million posts here on how to do this but since I'm such a
newbie, I don't know how to modify them to my explicit need.

I have a spreadsheet where I need to delete each ROW where the total in
Column O =0. I need to start the macro on Row 15 (due to headers) and need
it to extend to at least row 2500. I can't sort or modify the data at all
prior to running the macro.

Please send detailed instructions on how to put this into a macro...

thank you

~cr

delete rows using VB
 

Hi,

Try this macro.

Option Explicit

Sub deleterowswhereo0()

Dim ws As Worksheet
Dim lRowStart As Long, lRowEnd As Long, lRow As Long
Dim lColumn As Long

Application.ScreenUpdating = False

Set ws = ActiveSheet

lColumn = ws.Range("O1").Column
lRowStart = 15
lRowEnd = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1

For lRow = lRowEnd To lRowStart Step -1
If ws.Cells(lRow, lColumn).Value = 0 Then
ws.Rows(lRow).Delete
End If
Next lRow

Application.ScreenUpdating = True

End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"cindee" wrote:

I know there's a million posts here on how to do this but since I'm such a
newbie, I don't know how to modify them to my explicit need.

I have a spreadsheet where I need to delete each ROW where the total in
Column O =0. I need to start the macro on Row 15 (due to headers) and need
it to extend to at least row 2500. I can't sort or modify the data at all
prior to running the macro.

Please send detailed instructions on how to put this into a macro...

thank you

~cr

delete rows using VB
 

Hi CR,

Here's a macro to do what you want. Just install and run it. It'll ask you
to nominate the start row and test column.

Option Explicit

Sub CleanUp()
Dim CurrentRow As Long
Dim UsedRows As Range
Dim TopRow As Integer
Dim ColSelect As String
On Error GoTo Abort
TopRow = InputBox("What Row do you want to start at?", "Start Row", "1")
ColSelect = InputBox("What Column do you want to test?", "Test Column", "A")
Set UsedRows = ActiveSheet.UsedRange.Rows
For CurrentRow = UsedRows.Rows.Count To TopRow Step -1
If Range(ColSelect & CurrentRow).Value = 0 Then
' Use the following to delete the offending row:
UsedRows.Rows(CurrentRow).EntireRow.Delete
' Use the following two lines to hide the offending row:
'UsedRows.Rows(CurrentRow).EntireRow.Hidden = True
'Else UsedRows.Rows(CurrentRow).EntireRow.Hidden = False
End If
Next CurrentRow
' If only hiding for printing purposes, use the next two lines to print or
preview then restore the worksheet
'ActiveWindow.SelectedSheets.PrintPreview
'ActiveSheet.Rows.EntireRow.Hidden = False
Abort:
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


"cindee" wrote in message
...
I know there's a million posts here on how to do this but since I'm such a
newbie, I don't know how to modify them to my explicit need.

I have a spreadsheet where I need to delete each ROW where the total in
Column O =0. I need to start the macro on Row 15 (due to headers) and
need
it to extend to at least row 2500. I can't sort or modify the data at all
prior to running the macro.

Please send detailed instructions on how to put this into a macro...

thank you

~cr

delete rows using VB
 

Thank you! I love the ease of use here!

~cr

"macropod" wrote:

Hi CR,

Here's a macro to do what you want. Just install and run it. It'll ask you
to nominate the start row and test column.

Option Explicit

Sub CleanUp()
Dim CurrentRow As Long
Dim UsedRows As Range
Dim TopRow As Integer
Dim ColSelect As String
On Error GoTo Abort
TopRow = InputBox("What Row do you want to start at?", "Start Row", "1")
ColSelect = InputBox("What Column do you want to test?", "Test Column", "A")
Set UsedRows = ActiveSheet.UsedRange.Rows
For CurrentRow = UsedRows.Rows.Count To TopRow Step -1
If Range(ColSelect & CurrentRow).Value = 0 Then
' Use the following to delete the offending row:
UsedRows.Rows(CurrentRow).EntireRow.Delete
' Use the following two lines to hide the offending row:
'UsedRows.Rows(CurrentRow).EntireRow.Hidden = True
'Else UsedRows.Rows(CurrentRow).EntireRow.Hidden = False
End If
Next CurrentRow
' If only hiding for printing purposes, use the next two lines to print or
preview then restore the worksheet
'ActiveWindow.SelectedSheets.PrintPreview
'ActiveSheet.Rows.EntireRow.Hidden = False
Abort:
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


"cindee" wrote in message
...
I know there's a million posts here on how to do this but since I'm such a
newbie, I don't know how to modify them to my explicit need.

I have a spreadsheet where I need to delete each ROW where the total in
Column O =0. I need to start the macro on Row 15 (due to headers) and
need
it to extend to at least row 2500. I can't sort or modify the data at all
prior to running the macro.

Please send detailed instructions on how to put this into a macro...

thank you

~cr

delete rows using VB
 

Something like:


Sub DeleteRows()
Dim lastrow as Long, i as Long
lastrow = Worksheets("sheet1").Cells(rows.count,"O").Row
for i = lastrow to 15 step -1
if isnumeric(cells(i,"O")) then
if cells(i,"O") = 0 then
rows(i).Delete
end if
end if
Next
End Sub



--
Regards,
Tom Ogilvy


"cindee" wrote in message
...
I know there's a million posts here on how to do this but since I'm such a
newbie, I don't know how to modify them to my explicit need.

I have a spreadsheet where I need to delete each ROW where the total in
Column O =0. I need to start the macro on Row 15 (due to headers) and
need
it to extend to at least row 2500. I can't sort or modify the data at all
prior to running the macro.

Please send detailed instructions on how to put this into a macro...

thank you

~cr