Better way than "For Each... Next..." to replace 0s by blanks?

DavidExcel

New Member
Joined
Feb 12, 2019
Messages
10
I have wrote the code below, which has for objective to create a new file, which is a copy of the original one with the exception that prices are increased by a user define %.
I am not sure this the most efficient approach but it works.

The problem is actually with the last bit of code here (For Each... Next...). Since, there are so many lines, this takes forever.

Is there a better way to remove all 0 coming from the calculations and get blanks instead?


Code:
Private Sub IncreaseButton_Click()


    Dim FolderPath As String
    Dim FileName As String
    Dim FileCopyName As Variant


    FolderPath = Application.ActiveWorkbook.Path
    FileName = Application.ActiveWorkbook.Name
    
    FileCopyName = InputBox("Please enter the new file name after the price increase. It will be saved in the same folder." & vbCrLf & " " & vbCrLf & "Be aware that the Deal # and the validity dates will also be deleted from the new file.")
    
    If FileCopyName = "" Then
    MsgBox "DRF Creation Canceled"
    Exit Sub
    End If
    
    Dim IncOrDec As Variant
    IncOrDec = InputBox("Enter the percentage of Increase or Decrease without the % sign")
    
    If IncOrDec = "" Then
    MsgBox "The new DRF file cannot be created without a Value."
    Exit Sub
    End If
    
    If Not IsNumeric(IncOrDec) Then
    MsgBox "The new DRF file cannot be created without a numeric Value." & vbCrLf & "(Percentage value without the % sign)"
    Exit Sub
    End If
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveCopyAs (FolderPath & "\" & FileCopyName & ".XLSM")
       
    Dim StartFile As Excel.Workbook
    Set StartFile = Workbooks.Open(FolderPath & "\" & FileName)
    Dim EndFile As Excel.Workbook
    Set EndFile = Workbooks.Open(FolderPath & "\" & FileCopyName & ".XLSM")
    
    Dim StartPrice As Range
    Set StartPrice = StartFile.Worksheets("DRF").Range("D42:D10041")
    'Will need to be updated in final version
       
    Dim EndPrice As Range
    Set EndPrice = EndFile.Worksheets("DRF").Range("D42:D10041")
    'Will need to be updated in final version
       
    EndPrice = Evaluate(StartPrice.Address & "* (1+" & IncOrDec & "/100)")
        
[B]    For Each cell In EndPrice[/B]
[B]    If cell.Value = "0" Then cell.Value = Blank[/B]
[B]    Next[/B]
            
End Sub

Thanks for the help.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,055
Office Version
  1. 2016
Platform
  1. Windows
.
 
Last edited:

Swayzy

Board Regular
Joined
Mar 30, 2018
Messages
78
Try putting application.screenupdating = False at the start and re set it to true at rhe bottom
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,055
Office Version
  1. 2016
Platform
  1. Windows
With help of the macro recorder :
• Filter to display the zeroes
• Go to visible cells only
• Clear contents
• Remove the filter
 

DavidExcel

New Member
Joined
Feb 12, 2019
Messages
10

ADVERTISEMENT

What does this do exactly?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
You're right, the coding is definitely not the most efficient approach. Better may be to not put the zeros there at all. They presumably are created by the Evaluate applying to blank cells. One simple alternative to modify the current code

EndPrice.Cells.Replace What:=0, Replacement:=vbNullString, LookAt:=xlWhole
 

DavidExcel

New Member
Joined
Feb 12, 2019
Messages
10
You're right, the coding is definitely not the most efficient approach. Better may be to not put the zeros there at all. They presumably are created by the Evaluate applying to blank cells. One simple alternative to modify the current code

EndPrice.Cells.Replace What:=0, Replacement:=vbNullString, LookAt:=xlWhole

Sorry for the basic question, but where does this go?
Should I replace the evaluate line with that?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
It replaces the text in bold in the first post, David - as a better way than "for each... next ..." to replace 0s by blanks.
Did you write the code??
 

Watch MrExcel Video

Forum statistics

Threads
1,114,189
Messages
5,546,470
Members
410,742
Latest member
WalterSil
Top