Macro help in excel

[red sky]

I am trying to write a macro code in excel to delete any columns when a cell in that particular column = 0 (zero). That cell would start at B19 and subsequently follow to C19, D19 and so on. Any help would be appreciated.

[Shawnski]

Not sure if you are dealing with a lot of data and or columns or not, but at first blush, building a macro (that works) might take a lot longer time than is necessary.

You might try adding this to the bottom of each column (drag horizontally to all applicable columns)

If you have data say in B2 to B14, you might put in B15 the following =countif(B2:B14, "=0")

Then manually delete the columns that have a count >1.

Now if you go down the macro road, you would actually have to get into the VBA side, and it gets complicated.

[Kipru]

Originally Posted by Shawnski

Not sure if you are dealing with a lot of data and or columns or not, but at first blush, building a macro (that works) might take a lot longer time than is necessary.

You might try adding this to the bottom of each column (drag horizontally to all applicable columns)

If you have data say in B2 to B14, you might put in B15 the following =countif(B2:B14, "=0")

Then manually delete the columns that have a count >1.

Now if you go down the macro road, you would actually have to get into the VBA side, and it gets complicated.

It is not that complicated. Just use the "record macro" function on exel and you will get your macro done in no time, if you have any basic logical understanding. You can do the basic stuff with limited or none VBA skills.

[Shawnski]

Originally Posted by Kipru

It is not that complicated. Just use the "record macro" function on exel and you will get your macro done in no time, if you have any basic logical understanding. You can do the basic stuff with limited or none VBA skills.

Wrong answer.

I develop large applications in Excel and Access.

The "record macro" will not work in this case as there is human intervention to determine values that will then determine whether to delete a column or not.

If I was doing what I think the OP was doing, I would keep it as simple as possible (especially if it is a one off). If this is something more repetitive in nature, then you can look at the other options.

"Record macro" is NOT the answer.

[red sky]

I know the record macro will not work because the columns that will be deleted are not the same each time. A VBA code would need to be used but I have limited experience with that. I have looked online and it seems doable using Sub DeleteZeroColumns() and then a bunch of jargon after that. I was hoping the CP had an excel guru to help with this

[Bingo]

you can do it with a looping macro that evaluates each cell in a column until it reaches a break point.

but the rub is tayloring it to your specific needs.

this one works in our office to dump data up into a mktg system.

Dim detailLine As String

r = 1 'row designated for reading data
c = 1 'col designated for reading data

Do Until ThisWorkbook.Sheets("Upload").Cells(r + 1, c + 6) = ""
CSV = ThisWorkbook.Sheets("Upload").Cells(r + 1, c + 6)

Sheets("Upload").Select
detailLine = Range("G2")
'detailLine = "line of data as csv text"

import.ImportDetail ((detailLine))

If Flag = "" Then
Rows(r + 1).Select
Selection.Delete Shift:=xlUp
Else
r = r + 1
End If
'Loop 'until we run out of data
Loop

import.ImportEnd
Set import = Nothing
End Sub

[red sky]

I found out the vba code for it:

Sub DelC()
Dim LC As Integer, i As Integer
LC = Cells(20, Columns.Count).End(xlToLeft).Column
For i = LC To 2 Step -1
If Val(Cells(20, i).Value) = 0 Then Columns(i).Delete
Next i
End Sub