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.

 

[SteveO59L]

Why not just use a filter for 0 values?, then delete?

 

[jim may]

Try this Code in a Standard Module

*** Test Code on a BackUp Copy of your File, Not the Original ***

Sub Foo()
Dim Rng As Range
Dim LC As Long, TotCol As Long
LC = Cells(145, Columns.Count).End(xlToLeft).Column
    For i = LC To 1 Step -1
        If Cells(145, i).Value = 0 Then Cells(145, i).EntireColumn.Delete
    Next
TotCol = Range("A1").CurrentRegion.Columns.Count
Range("A1").AutoFilter Field:=TotCol, Criteria1:="0.00"  'Depending on your formatting here
Set Rng = Range("A1").CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible)
Rng.EntireRow.Delete
ActiveSheet.AutoFilter.ShowAllData
End Sub

 

[sheppy72]

Ok thanks for the replies so far.
I have found the following code that works great for removing the rows that the sum in column DT = 0.
However i really dont know how to change it so that it also removes the columns that have a value = 0 between column D and DT in row 145. Help would be very much appreciated.

Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Fill in the value that you want to delete
'Tip: use DeleteValue = "<>ron" to delete rows without ron
DeleteValue = 0

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("dt1:dt" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

End Sub

 

[Rick Rothstein]

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

Do you have both positive and negative numbers making up your sum? If so, won't removing rows and/or columns possibly affect other currently-non-zero rows and/or columns... maybe even making them sum to 0?

 

[sheppy72]

No all the data is positive in value. Basically many rows and also columns contain only 0 in all the cells between D17:DS17 to D145:DT144. The sum of the rows is on column DT and the sum from the columns in in row 145.
I need the sheet to be used in another workbook that can not accept any 0 values.
Any ideas?

 

[Rick Rothstein]

Give this (untested) code a try (I think I got all the references correct)...

Sub DeleteZeroSums()
  Dim LastRow As Long, LastCol As Long
  Range("A146:DT146").Value = Range("A145:DT145").Value
  Range("DU1:DU145").Value = Range("DT1:DT145").Value
  Rows(146).Replace 0, "=0", xlWhole
  Columns("DU").Replace 0, "=0", xlWhole
  On Error Resume Next
  Columns("DU").SpecialCells(xlCellTypeFormulas).EntireRow.Delete
  LastRow = Cells(Rows.Count, "DU").End(xlUp).Row
  Rows(LastRow).SpecialCells(xlCellTypeFormulas).EntireColumn.Delete
  LastCol = Cells(LastRow, Columns.Count).End(xlToLeft).Column
  Rows(LastRow).Clear
  Columns(LastCol).Clear
End Sub

 

[sheppy72]

Thanks but does not seem to do the job.

is there a way to paste the sheet in here for you to play with it so you know the references.

 

[Rick Rothstein]

is there a way to paste the sheet in here for you to play with it so you know the references.

You can post it online using one of these free posting websites...

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

Then post the URL they give you for the file back here.

 

[sheppy72]

here is the link
http://www.filedropper.com/book3_1

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).

Hope this helps someone figure this one out.