delete columns and rows-cells equalling zero or any selected value

[Guest]

Want to select a range of cells horizonally and then delete column when value
is zero. Same with rows, select cells vertically and then delete rows when
value is zero.
THX gang. I'd trade my mother for all your help...

 

[Bernie Deitrick]

Scottie,

I hope your mother doesn't visit microsoft.public.excel.worksheet.functions
;-)

Try the sub below.

HTH,
Bernie
MS Excel MVP


Sub ScottieDeleteZero()
Dim i As Integer
If Selection.Rows.Count > 1 And _
Selection.Columns.Count > 1 Then
MsgBox "Select either a single row or column of cells"
Exit Sub
End If
If Selection.Rows.Count > 1 Then
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i).Value = 0 Then
Selection.Cells(i).EntireRow.Delete
End If
Next i
Else
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i).Value = 0 Then
Selection.Cells(i).EntireColumn.Delete
End If
Next i
End If
End Sub

 

[Guest]

You're too damn good MVP...

Scottie,

I hope your mother doesn't visit microsoft.public.excel.worksheet.functions
;-)

Try the sub below.

HTH,
Bernie
MS Excel MVP


Sub ScottieDeleteZero()
Dim i As Integer
If Selection.Rows.Count > 1 And _
Selection.Columns.Count > 1 Then
MsgBox "Select either a single row or column of cells"
Exit Sub
End If
If Selection.Rows.Count > 1 Then
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i).Value = 0 Then
Selection.Cells(i).EntireRow.Delete
End If
Next i
Else
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i).Value = 0 Then
Selection.Cells(i).EntireColumn.Delete
End If
Next i
End If
End Sub