Menu

Problem with script in Excel [Solved/Closed]

Report
Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
 -
josh07429
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
See more

7 replies

Answer 1 / 7
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
 1576
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
Thank you
1
Thank you

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

CCM 5807 users have said thank you to us this month

Report
josh07429
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
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
 1576
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
josh07429
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
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
 1576
the above script will give you the exact (growing) number! You are always welcome!
Report
josh07429
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