Previous Topic Next Topic

Problem with script in Excel

Solved/Closed
josh07429 Posts 14 Registration date Friday June 23, 2017 Status Member Last seen November 20, 2017 - Updated on Jun 29, 2017 at 03:21 AM
josh07429 Posts 14 Registration date Friday June 23, 2017 Status Member Last seen November 20, 2017 - Jun 26, 2017 at 05:35 PM
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
Related:

1 response

Answer 1 / 1
Blocked Profile
Jun 26, 2017 at 04:39 PM
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
josh07429 Posts 14 Registration date Friday June 23, 2017 Status Member Last seen November 20, 2017
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 :)
Blocked Profile
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
josh07429 Posts 14 Registration date Friday June 23, 2017 Status Member Last seen November 20, 2017
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 ;)
Blocked Profile
Jun 26, 2017 at 04:58 PM
the above script will give you the exact (growing) number! You are always welcome!
josh07429 Posts 14 Registration date Friday June 23, 2017 Status Member Last seen November 20, 2017
Jun 26, 2017 at 05:35 PM
I will try the script.

Many thanks :))

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
GOM Player Error
vista.ultimate.64 -
abrock -

8 responses
cannot find script file : "c:/windows/system3
nani -
Ambucias -

1 response
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
Windows Script Host: "Can't find script file..."
Moss -
xpcman -

3 responses