Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
delete columns if 0
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.De lete 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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
delete columns if 0
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) On Thu, 1 Jan 2009 14:50:07 -0800, jatman wrote: 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.De lete 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
delete columns if 0
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(xlCell TypeBlanks).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(xlCellTypeFor mulas, 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 "jatman" wrote: 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.De lete 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Columns | New Users to Excel | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
Can't delete columns | Excel Discussion (Misc queries) | |||
I can't delete columns - help! | Excel Worksheet Functions | |||
Delete Columns not Containing Certain Text | Excel Worksheet Functions |