Offset(-1)).SpecialCells(xlCellTypeFormulas) If c.Value = 0 Then cf = c.Formula Union(c, Range(Replace(Mid(cf, 13, Len(cf)), ")", ""))).EntireRow. Code: Sub test () lr Cells (Rows.Count, 'A').End (xlUp).Row For r lr To 2 Step -1 Set rng Cells (r, 'A') cf rng.Formula cv rng. It also assumes the first row is a header. Sub DelZeroBlocks() Dim c As Range Dim cf As String For Each c In Range("X2", Range("X" & Rows.Count).End(xlUp) _. It assumes the values and subtotals are in column A and the whole row should be deleted. In that case an adaptation something like this might be useful: This would avoid checking the Grand Total row and therefore avoid the possibility of deleting virtually the whole table if the Grand Total was zero (even though none of the individual subtotals might be zero).Īlso, if the original column X data does not contain formulas, the efficiency of the code could be increased by only looking at cells that contain formulas rather than checking every row. I would suggest adding "-1" to the line that calculates lr.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |