Deleting 0 after refresh

[Romano_odK]

Good afternoon,

In a table I have the columns C until O who are filled from a SQL after a refresh. In that refresh some cells are filles with a "zero". Is it possible to erase all the cells where there is only one zero?


Thank you for you time and efforts.

Directory:	C:\temp\100MRP.xml	Last refresh		Date	Week
		22/02/2024 17:00		22/02	8
Artikelcode	Omschrijving	02/2023	03/2023	04/2023	05/2023	06/2023	07/2023	08/2023	09/2023	10/2023	11/2023	12/2023	01/2024	02/2024
itemcode	description	q-12	q-11	q-10	q-9	q-8	q-7	q-6	q-5	q-4	q-3	q-2	q-1	q-0
100000	Vito Glaserfix 111 6x2 mm wit - 10x25 m	16	16	16	16	50			18	21	5	32	13	17
100001	Vito Glaserfix 111 6x2 mm zwart - 10x25 m		80					16			36	16	26	17
100002	Vito Glaserfix 111 6x3 mm wit - 10x25 m	80	62	17		32	48		18	37	16	32	3	32
100003	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	48	21	1		21	16	32	16		21	17	7
100004	Vito Glaserfix 111 6x4 mm wit - 10x25 m	16	-16					16		16
100005	Vito Glaserfix 111 6x4 mm zwart - 10x25 m	16	-16					16

 

[Joe4]

Why not just use a Custom format on those cells of:
0;-0;;@
to hide those zero values?

It is also shown here in the "Hide zero values in selected cells" area here: Display or hide zero values - Microsoft Support

 

[Romano_odK]

Why not just use a Custom format on those cells of:
0;-0;;@
to hide those zero values?

It is also shown here in the "Hide zero values in selected cells" area here: Display or hide zero values - Microsoft Support

No I really need empty cells, not hiding.

 

[Joe4]

No I really need empty cells, not hiding.

Can you explain for what purpose?
If we know exactly what you need to do with them, and why they need to be this way, we can provide you with the best answer for your situation.

 

[Romano_odK]

Can you explain for what purpose?
If we know exactly what you need to do with them, and why they need to be this way, we can provide you with the best answer for your situation.

This piece is part of a large sheet that is used to eventually import an XML into our propriety software. A "zero" has a value for a SQL server and so this collides with the software we use, its needs to be empty for that. I do know how to explain it otherwise, but with a refresh these zero's are downloaded from the SQL server but do not need to go back.

 

[Joe4]

OK, how about just selecting the entire range that you want to apply it to, and do a Find/Replace, replacing 0 with nothing?
As long as you check the "Match entire cell contents" check box, it will only remove the entries that have EXACTLY 0 in them, and not any that have zeroes in larger numbers (like 10, 107, etc).

 

[Romano_odK]

OK, how about just selecting the entire range that you want to apply it to, and do a Find/Replace, replacing 0 with nothing?
As long as you check the "Match entire cell contents" check box, it will only remove the entries that have EXACTLY 0 in them, and not any that have zeroes in larger numbers (like 10, 107, etc).

View attachment 107317

We now erase all the cells by hand this way, but it would be a great benefit if this was an automated process. Since we have to do this all the time since we refresh this sheet very often. So that's why it would be a great help if this was done automatically.

 

[Joe4]

We now erase all the cells by hand this way, but it would be a great benefit if this was an automated process. Since we have to do this all the time since we refresh this sheet very often. So that's why it would be a great help if this was done automatically.

You can do the Find/Replace method with VBA. If you turn on your Macro Recorder and record yourself doing those steps manually, you will have most of the VBA code you need to do this.
(The Macro Recorder is a great tool to get VBA code, especially for people who are new to VBA - I highly recommend you start using it!).

If you need to make it more dynamic based on changing range sizes, then you will need to tell us how we can determine the size of the range to apply it to.
- What columns should be included?
- What row does the data you want to replace start on?
- What column can we look at to determine the last row of data that needs to be included (for example, if every row with data has something in column A, then we can look at column to determine the last row of data)?

 

[Romano_odK]

You can do the Find/Replace method with VBA. If you turn on your Macro Recorder and record yourself doing those steps manually, you will have most of the VBA code you need to do this.
(The Macro Recorder is a great tool to get VBA code, especially for people who are new to VBA - I highly recommend you start using it!).

If you need to make it more dynamic based on changing range sizes, then you will need to tell us how we can determine the size of the range to apply it to.
- What columns should be included?
- What row does the data you want to replace start on?
- What column can we look at to determine the last row of data that needs to be included (for example, if every row with data has something in column A, then we can look at column to determine the last row of data)?

Good morning, thank you for your replay. I will start with that and see how this goes. Thank you and have a great day.