How to ferase value in column "Aantal" if "Locatie" is not "A00"

[Romano_odK]

Good afternoon,

I got a table with values that are refreshed from a SQL database. I get lines with itemcode, description etc where the stock is in column "J" (Voorraad) on every location (Locatie). What I would like to accomplish is that I won't see the value in column "Aantal" if the "Locatie" in column "I' is not "A00".
This has to be done every time I refresh the table, can this be done?

Thank you for you time and efforts.

Romano

Artikelcode	Omschrijving	Kostprijs	Vrrdrek	Ontvgstdatum	Aantal	Magazijn	Std_locatie	Locatie	Voorraad
100000	Vito Glaserfix 111 6x2 mm wit - 10x25 m	1	3901	11/07/2023	96	1	P02B	A00	121
100001	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	2	3901	18/04/2023	96	1	P04B	P04B	106
100002	Vito Glaserfix 111 6x3 mm wit - 10x25 m	3	3901	11/07/2023	96	1	P02B	P02B	63
100003	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	4	3901	25/04/2023	96	1	P04B	P04B	40
100004	Vito Glaserfix 111 6x4 mm wit - 10x25 m	5	3901	06/09/2023	96	1	P02B	P02B	97
100004	Vito Glaserfix 111 6x4 mm wit - 10x25 m	6	3901	06/09/2023	96	1	P02B	P02C	60
100005	Vito Glaserfix 111 6x4 mm zwart - 10x25 m	7	3901	31/03/2021	96	1	A00	P04B	53
100006	Vito Glaserfix 111 9x2 mm wit - 10x25 m	8	3901	18/07/2023	60	1	P01B	P01B	20
100007	Vito Glaserfix 111 9x2 mm zwart - 10x25 m	9	3901	16/08/2023	60	1	P03B	P03B	118

 

[Joe4]

You could add an additional column, that has a formula something like:

=IF(I2="A00",J2,"")

or

=IF(I2="A00",J2,0)

depending on whether you want to show 0 or nothing at all when the value in column I is not "A00".

Then you could hide the current column J.
You might also be able to add this calculation right on the SQL side, so it comes over the way you want.

Or are you looking for some VBA code to run against this that actually removes the values from column J?

 

[Romano_odK]

You could add an additional column, that has a formula something like:

=IF(I2="A00",J2,"")

or

=IF(I2="A00",J2,0)

depending on whether you want to show 0 or nothing at all when the value in column I is not "A00".

Then you could hide the current column J.
You might also be able to add this calculation right on the SQL side, so it comes over the way you want.

Or are you looking for some VBA code to run against this that actually removes the values from column J?

Thank you for reply,
I think it would be cleaner to use VBA here, but if that answer won't come then I would indeed consider your solution.

 

[Joe4]

If looking for VBA code, maybe something like this:

Sub MyRemoveValues()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column J
    lr = Cells(Rows.Count, "J").End(xlUp).Row
    
'   Loop through all rows starting on row 2
    For r = 2 To lr
'       Check value in column I
        If Cells(r, "I") <> "A00" Then Cells(r, "J").ClearContents
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[Romano_odK]

If looking for VBA code, maybe something like this:

Sub MyRemoveValues()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row with data in column J
    lr = Cells(Rows.Count, "J").End(xlUp).Row
   
'   Loop through all rows starting on row 2
    For r = 2 To lr
'       Check value in column I
        If Cells(r, "I") <> "A00" Then Cells(r, "J").ClearContents
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

I tried it but for some reason it erases all the values in column "J". Any idea why this may happen?

 

[Joe4]

I tried it but for some reason it erases all the values in column "J". Any idea why this may happen?

Usually that means that your values in column I are not really "A00".
Perhaps you have an extra space or other character in there.

If you find one of the rows that contain "A00" in column I, what happens if you enter this formula in some blank cell (let's sy "A00" is in cell I2):

=LEN(I2)

If that formula returns anything greater than 3, you have other characters in that cell.

 

[Romano_odK]

I get the value "10".

 

[Joe4]

So, it sounds like you have a LOT of extra spaces in there!

Try modifying the code like this, to only check the first 3 characters (I am assuming the extra characters are at the end, and not the beginning):

Sub MyRemoveValues()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column J
    lr = Cells(Rows.Count, "J").End(xlUp).Row
    
'   Loop through all rows starting on row 2
    For r = 2 To lr
'       Check first 3 characters in column I
        If Left(Cells(r, "I"),3) <> "A00" Then Cells(r, "J").ClearContents
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[Romano_odK]

So, it sounds like you have a LOT of extra spaces in there!

Try modifying the code like this, to only check the first 3 characters (I am assuming the extra characters are at the end, and not the beginning):

Sub MyRemoveValues()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row with data in column J
    lr = Cells(Rows.Count, "J").End(xlUp).Row
   
'   Loop through all rows starting on row 2
    For r = 2 To lr
'       Check first 3 characters in column I
        If Left(Cells(r, "I"),3) <> "A00" Then Cells(r, "J").ClearContents
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

Good evening,
It works, thank you help. You have made my day.

Romano

 

[Romano_odK]

Good evening,
It works, thank you help. You have made my day.

Romano

Good morning,
Got one question, if the value in question is not in the sheet I get an error. Is there a way to prevent that?