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
1
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 :)
0
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
0
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 ;)
0
Blocked Profile
Jun 26, 2017 at 04:58 PM
the above script will give you the exact (growing) number! You are always welcome!
0
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 :))
0

Similar discussions

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

6 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

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

3 responses
Anu Script Manager 7 software for Windows 7
neelamnagaraja -
xpcman -

1 response