Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba to delete rows
I have a worksheet "Report" that is populated with revenue and expense
information by referencing a pivot table. When I select a new pivot table page (department), the proper accounts and amounts appear on "Report". Different departments have different numbers of revenue and expense rows, so "Report" has lots of rows of each type to accomodate any department. I have a macro that copies the data to another sheet called "Report (2)" so the original "Report" will stay intact. On "Report (2)" I am trying to remove unnecessary blank rows. I am a VBA newbie, so I copied some code from another thread and modified it. It works, but it only removes six empty rows. If I run it again, it removes six more empty rows. I can't figure out why it only removes six empty rows at a time. Here's the code for the revenue area: 'Remove blank revenue rows Sub DeleteRows() Set currentCell = Worksheets("Report (2)").Range("M12") For Each cell In Range("M12:M28") Set nextCell = currentCell.Offset(1, 0) If Value(currentCell.Value) = 0 Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Next End Sub Column M contains a value of zero only if certain cells in the row are blank. If so, the entire row should be removed. The revenue area on "Report (2)" is from row 12 to row 28. Could anyone help a novice? Thanks, Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba to delete rows
when you loop forward in this fashion as a row is deleted, the remaining rows
slide down one row, so the next row is skipped (it is never examined). So you need to loop from highest to lowest. (come up from the bottom) sub Delete rows Dim i as Long with Worksheets("Report (2)") for i = 28 to 12 step -1 if isnumeric(.cells(i,"M")) then if Int(.cells(i,"M")) = 0 then .rows(i).Delete end if end if Next End With End sub -- Regards, Tom Ogilvy "Tripper" wrote: I have a worksheet "Report" that is populated with revenue and expense information by referencing a pivot table. When I select a new pivot table page (department), the proper accounts and amounts appear on "Report". Different departments have different numbers of revenue and expense rows, so "Report" has lots of rows of each type to accomodate any department. I have a macro that copies the data to another sheet called "Report (2)" so the original "Report" will stay intact. On "Report (2)" I am trying to remove unnecessary blank rows. I am a VBA newbie, so I copied some code from another thread and modified it. It works, but it only removes six empty rows. If I run it again, it removes six more empty rows. I can't figure out why it only removes six empty rows at a time. Here's the code for the revenue area: 'Remove blank revenue rows Sub DeleteRows() Set currentCell = Worksheets("Report (2)").Range("M12") For Each cell In Range("M12:M28") Set nextCell = currentCell.Offset(1, 0) If Value(currentCell.Value) = 0 Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Next End Sub Column M contains a value of zero only if certain cells in the row are blank. If so, the entire row should be removed. The revenue area on "Report (2)" is from row 12 to row 28. Could anyone help a novice? Thanks, Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba to delete rows
When deleting rows you should always work backwards. This is what is happening:
Say Row 12 and 13 are blank, you test row 12, it is blank so you delete it, now row 13 becomes row 12 and contains a blank, but you tell excel to go to the next row, 13 and evaluate it, thus the original row 13 never got evaluated/deleted. Try this macro: Sub DeleteRows() Dim cnt As Long For cnt = 28 to 12 Step - 1 If Range("M" & cnt) = 0 Then Rows(cnt).Delete Next End Sub -- Charles Chickering "A good example is twice the value of good advice." "Tripper" wrote: I have a worksheet "Report" that is populated with revenue and expense information by referencing a pivot table. When I select a new pivot table page (department), the proper accounts and amounts appear on "Report". Different departments have different numbers of revenue and expense rows, so "Report" has lots of rows of each type to accomodate any department. I have a macro that copies the data to another sheet called "Report (2)" so the original "Report" will stay intact. On "Report (2)" I am trying to remove unnecessary blank rows. I am a VBA newbie, so I copied some code from another thread and modified it. It works, but it only removes six empty rows. If I run it again, it removes six more empty rows. I can't figure out why it only removes six empty rows at a time. Here's the code for the revenue area: 'Remove blank revenue rows Sub DeleteRows() Set currentCell = Worksheets("Report (2)").Range("M12") For Each cell In Range("M12:M28") Set nextCell = currentCell.Offset(1, 0) If Value(currentCell.Value) = 0 Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Next End Sub Column M contains a value of zero only if certain cells in the row are blank. If so, the entire row should be removed. The revenue area on "Report (2)" is from row 12 to row 28. Could anyone help a novice? Thanks, Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba to delete rows
Thanks so much for the quick responses. Now it makes perfect sense to
me. Dan |
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 |