find and delete

[sheppy72]

Hi Forum,

I have what I hope is a simple question that you can help me with.
I have a sheet that has data in all cells from A to DT and down to row 145.
In row 145 is a sum of the data in the rows above, and in DT is the sum of the data in the columns before.

I need to rationalise this to remove all rows and columns where the sum equals zero.

what should the vba code be to automate this process. Any help much appreciated.

 

[sheppy72]

Think i may have got the link wrong.. Second attempt

<a href=http://www.filedropper.com/book3_1><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >file upload storage</a></div>

 

[Rick Rothstein]

I need to keep the rows 14 & 15 and column A & B then remove all rows that the sum is 0 (sum shown in row 145) and also remove all coloumns where the sum is 0 (sum in coloumn DT).

Well, no wonder none of our code worked. In your original message you said...

"I have a sheet that has data in all cells from A to DT and down to
row 145. In row 145 is a sum of the data in the rows above, and in
DT is the sum of the data in the columns before."

But now, you file shows your data table starts in Column D, not A... and your data table starts at Row 17 whereas your original message hinted at Row 1.

Okay, with that said, give this code a try...

Sub DeleteZeroSums()
  Dim LastRow As Long, LastCol As Long
  Range("D146:DS146").Value = Range("D145:DS145").Value
  Range("DU17:DU144").Value = Range("DT17:DT144").Value
  Range("D146:DS146").Replace 0, "=0", xlWhole
  Range("DU17:DU144").Replace 0, "=0", xlWhole
  On Error Resume Next
  Range("DU17:DU144").SpecialCells(xlCellTypeFormulas).EntireRow.Delete
  LastRow = Cells(Rows.Count, "DT").End(xlUp).Row + 1
  Range("D" & LastRow & ":DS" & LastRow).SpecialCells(xlCellTypeFormulas).EntireColumn.Delete
  LastCol = Cells(LastRow, Columns.Count).End(xlToLeft).Column
  Rows(LastRow).Clear
  Columns(LastCol + 2).Clear
End Sub

 

[sheppy72]

Thank you, that works great. Sorry about the wrong reference before by the way.

 

[sheppy72]

Rick, If your around can you help with your code again please.
Please see my new thread.

http://www.mrexcel.com/forum/showthread.php?t=576469