We help IT Professionals succeed at work.

# How can I modify this macro in Excel?

on
I would like to modify the following macro.

``````Sub Step3()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If Selection.Cells(i, 1) = "" And (Selection.Cells(i, 2) = 0 Or _   Selection.Cells(i, 2) = "0.00") Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
``````

I have a spreadsheet with 2 columns, Column A and Column B.
Column A usually has numbers such as 12345.  Column B at times is blank or has numbers.

In the following scenario, if it finds a number, in the spreadsheet, say 99999:

``````99999	    0.00
1,898.00
2.00
2000.00
12345
``````
In the scenario above, when it finds a number which has some 'corresponding' blank fields underneath it, yet has some
numbers in Field B.  It will assume that the last blank row, before another number begins, is the Total.  The script should then move the last number it finds in the first row and insert a 0 in the other rows.

In other words it should end up looking like this.
``````99999  2000.00
0
0
12345
``````
Comment
Watch Question

## View Solution Only

Top Expert 2016

Commented:
Hi,

pls try
``````Sub Step4()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If Selection.Cells(i, 1) <> "" Then
Selection.Cells(i, 2) = Total
Else
If Selection.Cells(i + 1, 1) <> "" Then
Total = Selection.Cells(i, 2)
End If
Selection.Cells(i, 2) = 0
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
``````
Regards
Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
Try this:

``````Sub Step3()
Dim i As Long, j As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
For i = 1 To Selection.Rows.Count
If Selection.Cells(i, 1) = "" Then
Selection.Cells(i, 2) = 0
Else
j = i + 1
Do Until Selection.Cells(j, 1) <> "" Or Selection.Cells(j, 2) = ""
j = j + 1
Loop
Selection.Cells(i, 2) = Selection.Cells(j - 1, 2)
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
``````
Top Expert 2014

Commented:
@100questions
Why zero the values?  It seems to me that the rows should be deleted.