Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Problem with script in Excel [Solved/Closed]

Report
Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
 -
Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
 -
Hello,

I have a problem with my script. What I want is IF the value in column J is equals to "0" then I want the column H to be blank.

Ex. J1 cell has 0, I want H1 to be blank

The problem with my script is if Column J has 10 20 or anything with zero it gets deleted as well.. I just want to delete the zero and just leave the 10 20 etc not to be deleted.

Here is my script I know it just needs a little tweak.

"Dim b As Long
For b = 1 To 65536
If InStr(1, ActiveSheet.Range("$J$" & b), "0") > 0 Then
ActiveSheet.Range("$H$" & b) = ""
End If
Next

End Sub"

Thanks in advance

1 reply

Answer 1 / 1

Try this: 

DIM B
DIM CellRangeToCheck
DIM cellRangeToBlank
for b = 1 to 10
CellRangeToCheck = "J" & b 
CellRangeToBlank ="H" & b
cellvalue =  ActiveSheet.Range(CellRangeToCheck).value
  
If cellvalue = "" then ActiveSheet.Range(CellRANGEtoBlank).value = ""
next
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report
Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
I used this and it worked fine but a little bit slow to run

Dim b As Long
For b = 1 To 5000
If (ActiveSheet.Range("$J$" & b) = 0) Then
ActiveSheet.Range("$H$" & b) = ""
End If
Next
End Sub

I appreciate the reply :)
Report
Blocked Profile
Yes, it will be slow. Try to limit the count to only what you know is filled, as in: 


b = Cells(ThisWorkbook.Worksheets(WS).Rows.Count, 1).End(xlUp).Row
Report
Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
yes I started with 65536 but I changed it to 5000, I dont really have an exact amount of rows because each data is different from others some will get low some high but I figured 5000 is the safe number for now. thanks for the help ;)
Report
Blocked Profile
the above script will give you the exact (growing) number! You are always welcome!
Report
Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
I will try the script.

Many thanks :))
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!