delete columns if 0

[jatman]

i am using the following code to delete the complete row:

Range("B:B").Select
Selection.AutoFilter Field:=1, Criteria1:="0"
On Error GoTo line1
With Sheet1.Range("b:b")
Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
line1:

what i need is to delete only columns a:g (not the entire row) if the value
in column b = 0.

any help is appreciated.

jat

 

[Chip Pearson]

Try some code like the following. Change the line

Set WS = Worksheets("Sheet1") '<<< Change to appropriate sheet

to the appropriate worksheet. Change the line

TopRow = 1 '<<< change to first row number with data

to the first row number that might have data to delete. This assumes
that when you say "column b = 0" you mean that it has an actual value
of 0, not a default value of 0 as a result of being empty. This code
will not delete rows in which column B is empty.


Sub AAAA()
Dim RowNdx As Long
Dim LastRow As Long
Dim WS As Worksheet
Dim TopRow As Long

Set WS = Worksheets("Sheet1") '<<< Change to appropriate sheet
TopRow = 1 '<<< change to first row number with data

With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To TopRow Step -1
If Len(.Cells(RowNdx, "B").Text) > 0 Then
If .Cells(RowNdx, "B").Value = 0 Then
.Cells(RowNdx, "A").Resize(, 7).Delete
End If
End If
Next RowNdx
End With
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Shane Devenshire]

Hi,

Here is code to delete all rows which have blank cells in column A. You can
modify this to delete all cells of many different types and for any column.

Sub DeleteRows()
Range("A1:A" &
Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Similar code for deleting Columns that all contain formulas which evaluate
to numbers on row 2 would look like this:

Sub DeleteColumns()
Range("B2:" &
[IV2].End(xlToLeft).Address).SpecialCells(xlCellTypeFormulas,
1).EntireColumn.Delete
End Sub

And the code to address your specific example:

Sub DeleteIfZero()
With Range("B1:B" & [B65536].End(xlUp).Row)
Set c = .Find(0, LookIn:=xlValues)
If Not c Is Nothing Then
[A:G].Delete
End If
End With
End Sub

Note as asked you want to delete the entire columns A:G if any cell in
column B contains 0. I'm not sure that my interpretation of your problem is
correct.

If this helps, please click the Yes button

Cheers,
Shane Devenshire