Deleting columns where a cell within contains a value

[burlywood66]

I want to delete columns that contain a 0 value in row 2 of the column.

In my case, I have headers in row 1 and values in rows 2 and below. I eventually want to filter out the columns that contain no values below the headers.

Let me know if you need any more information for clarification.

Thanks

[JBeaucaire]

With no further information, you would have to LOOP through row2 and evaluate each cell to determine if there is a value or not.

But if the cells in row2 are actually EMPTY, then this can be done pretty quickly with no loop:

Please Login or Register  to view this content.

If that works, mark this as solved. If not, if row 2 is filled with formulas that result in zero values, we would have to loop. Let me know.

[burlywood66]

I think I have to stick with the 0's. Right now, I have 16 rows of data, and the number of rows below it ranges from 1 (with value 0) to upwards of 20 or 30, so I'm not sure of the best way to do it. If you don't mind, could you build this code off of the following:

F19:U19 contains headers
F20:U20 contains the 0 or the first value
and I guess F50:U50 would contain the last possible value for the respective column

If deleting the columns is too difficult, you could return a dataset where the non 0 columns are copied to column V and over.

Let me know if that helps, and thank you very much for your assisstance.

[JBeaucaire]

Hey Burly, this isn't hard, just got unclear all of a sudden.

Go ahead an put up a sample workbook demonstrating exactly what the data looks like, meaning don't oversimplify it. A Before/After mockup would be clearest of all, yes?

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[burlywood66]

The upload may not go through because I am at work, but inevitably, I want to do the following:

If you hit print preview, you will see the table that is in the spreadsheet. I eventually want to show below that, a summary of all of the calculations that went into the cells in column C.

So for instance, below, I would see calculations for Labels B,D,F,I,M,P and S as they contain values.

So I would like to see:

B
1
2
3

D
2
3
4

and so on.

The point where I was at, was after hitting the Transpose button, I had data being sorted a certain way, where I would eventually copy/paste that back below the main Table. But I would rather it all be automated, where I can click a button, and any cells in column C with data would show below the table with the numbers within each calclulation.

Please let me know if you need any clarification.

Thanks a lot!

[burlywood66]

Also,
If you view the page break preview, you'll see that the page cuts off at row 52. You can have the data show up, starting in row C, and making its way over to D, E and F, with row 52 being the cut off point.

[JBeaucaire]

Still not sure, but we can do some things here.

First, all the "selecting" can be taken out. The macro recorder shows "selecting" because it's recording your human actions. You can always merge the multiple commands commands to take out the "selecting" and issue the commands directly to the range(s) they apply to. Much faster.

Then, I just added a routine at the bottom that looks for all the zeros in 20 and deletes those columns. Does that work for you?

Please Login or Register  to view this content.

[burlywood66]

Thanks! That worked great!

Can I ask you one more thing: Is there a way to get the values to transpose into one column, or running through 4 different columns as I asked in my most previous post. From here, I would want the information that is displayed in cell F19 to show in A26 and below.

[JBeaucaire]

A little tweaking to both macros:

Please Login or Register  to view this content.

The Application stuff will eliminate the screen flicker and make the macro appear almost instantaneous.

[burlywood66]

Wow, you are clearly the man. I can't thank you enough. Best of luck

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)