Previous Topic Next Topic

Problem with script in Excel

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

Réponse 1 / 1
Blocked Profile
26 Jun 2017 à 16:39
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 23 June 2017 Status Member Last seen 20 November 2017
26 Jun 2017 à 16:48
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
26 Jun 2017 à 16:51
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 23 June 2017 Status Member Last seen 20 November 2017
26 Jun 2017 à 16:54
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
26 Jun 2017 à 16:58
the above script will give you the exact (growing) number! You are always welcome!
josh07429 Posts 14 Registration date Friday 23 June 2017 Status Member Last seen 20 November 2017
26 Jun 2017 à 17:35
I will try the script.

Many thanks :))

Similar discussions

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

6 responses
Convert number to spelling
Sukumar -
mrwagh85 -

28 responses