We help IT Professionals succeed at work.

How can I modify this macro in Excel?

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

Open in new window


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

Open in new window

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

Open in new window

Comment
Watch Question

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

Open in new window

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

Open in new window

Top Expert 2014

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