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

S

Scottie

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

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
 
S

Scottie

You're too damn good MVP...

Bernie Deitrick said:
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
Click to expand...
 
Top