Report

Problem with script in Excel [Solved]

Ask a question josh07429 15Posts Friday June 23, 2017Registration date July 7, 2017 Last seen - Last answered on Jun 26, 2017 at 05:35 PM by josh07429
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
Helpful
+1
plus moins
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


Was this answer helpful?  
josh07429 15Posts Friday June 23, 2017Registration date July 7, 2017 Last seen - Jun 26, 2017 at 04:48 PM
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 :)
Reply
ac3mark 7998Posts Monday June 3, 2013Registration date ModeratorStatus October 17, 2017 Last seen - Jun 26, 2017 at 04:51 PM
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
Reply
josh07429 15Posts Friday June 23, 2017Registration date July 7, 2017 Last seen - Jun 26, 2017 at 04:54 PM
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 ;)
Reply
ac3mark 7998Posts Monday June 3, 2013Registration date ModeratorStatus October 17, 2017 Last seen - Jun 26, 2017 at 04:58 PM
the above script will give you the exact (growing) number! You are always welcome!
Reply
josh07429 15Posts Friday June 23, 2017Registration date July 7, 2017 Last seen - Jun 26, 2017 at 05:35 PM
I will try the script.

Many thanks :))
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!