How to add sum value for odd number rows in Excel VBA

Question

I need help with my VBA code, I want to get the total value (display in Sheets("Report").Cells(LastLine,i).Value) of each odd row. In my code, I only can get total odd and even row values. Thanks!
Here is my VBA code:

 'LastLine is a row number which have blank content
    Dim LastLine As Long
    LastLine = Range("B" & Rows.count).End(xlUp).Row + 2

   For i = 4 To 21
    Sheets("Report").Cells(LastLine, y).Select
      With Selection
          .Font.Bold = True
          .Font.Size = 10
          .Interior.Color = RGB(135, 206, 250)
      End With
     Sheets("Report").Cells(LastLine, i).Value = WorksheetFunction.Sum(Range(Cells(2, i), Cells(65536, i)))
    Next

Use the MOD function of vb on the row index. See [this][1] link. [1]: http://stackoverflow.com/questions/15638423/excel-odd-rows-give-value — ilans, Jan 27 '15 at 15:49

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

First NEVER use Select

Let's try this:

'LastLine is a row number which have blank content
    Dim LastLine As Long, RunSum As Long
    LastLine = Range("B" & Rows.count).End(xlUp).Row + 2

 For i = 4 To 21
    With Sheets("Report").Cells(LastLine, y) 'Perhaps (LastLine, i)?
        .Font.Bold = True
        .Font.Size = 10
        .Interior.Color = RGB(135, 206, 250)
    End With
    RunSum = 0
    For CurRow = 3 to LastLine - 1 Step 2
        RunSum = RunSum + Cells(CurRow, i).Value
        Sheets("Report").Cells(LastLine, i).Value = RunSum
    Next CurRow
Next i

edited May 23 '17 at 11:57

Community

1
1

answered Jan 27 '15 at 15:55

Chrismas007

6,085
4
24
47

Yes! You are awesome! It works! Thank you so much~ I am new with VBA. BTW, why never use "Select"? – Xi Wang Jan 27 '15 at 16:07

How to add sum value for odd number rows in Excel VBA

1 Answers1