Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Delete rows with numeric values, leave rows with text | Excel Programming | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |