Attempting to write a simple macro that finds the LastRow in Column "CO". In the row below the LastRow, I want to add 1 to the value of the cell above it looping through all the cells in the LastRow to the last column (last cell of the LastRow). I may add more numbers to the LastRow in the future. Once I loop through last cell of the LastRow, I want it to stop looping.
The macro I have below stops at the first cell of the blank row below the last row. I'm stuck.
I'm sure there is a simpler macro that will accomplish my task than the monstrosity I wrote below.
Any suggestions would be appreciated.
The macro I have below stops at the first cell of the blank row below the last row. I'm stuck.
I'm sure there is a simpler macro that will accomplish my task than the monstrosity I wrote below.
Any suggestions would be appreciated.
LatestNC5_3.10.23.xlsm | |||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
CO | CP | CQ | CR | CS | CT | CU | CV | CW | CX | CY | CZ | DA | DB | DC | DD | DE | DF | DG | DH | DI | DJ | DK | DL | DM | DN | DO | DP | DQ | DR | DS | DT | DU | DV | DW | DX | DY | DZ | EA | EB | EC | ED | EE | |||
1374 | |||||||||||||||||||||||||||||||||||||||||||||
1375 | 6 | 11 | 13 | 1 | 2 | 13 | 4 | 3 | 1 | 14 | 20 | 5 | 0 | 0 | 0 | 6 | 2 | 1 | 0 | 2 | 2 | 7 | 1 | 3 | 17 | 22 | 12 | 14 | 9 | 6 | 12 | 3 | 11 | 31 | 3 | 4 | 1 | 0 | 14 | 20 | 8 | 5 | 18 | ||
1376 | 7 | 12 | 14 | 2 | 3 | 14 | 5 | 4 | 2 | 15 | 21 | 6 | 1 | 1 | 1 | 7 | 3 | 2 | 1 | 3 | 3 | 8 | 2 | 4 | 18 | 23 | 13 | 15 | 10 | 7 | 13 | 4 | 12 | 32 | 3 | 4 | 2 | 1 | 15 | 21 | 9 | 6 | 19 | ||
1377 | |||||||||||||||||||||||||||||||||||||||||||||
1378 | |||||||||||||||||||||||||||||||||||||||||||||
SBH (2) |
VBA Code:
Sub SBHTest()
Dim lastRow As Long
lastRow = Cells(Rows.Count, "CO").End(xlUp).Row ' find the last row of values in column CO
Dim firstBlankRow As Long
firstBlankRow = lastRow + 1 ' get the first blank row below the last row of values in column CO
Cells(firstBlankRow, "CO").Value = lastRow + 1 ' leave a value only in the first cell of the blank row without the formula
Dim lastCol As Long
lastCol = Cells(lastRow, Columns.Count).End(xlToLeft).Column ' find the last column of values in the last row
Dim i As Long, j As Long
For i = lastRow To firstBlankRow Step -1 ' loop through the rows from the last row down to the first blank row below the last row of values in column CO
For j = 93 To lastCol ' loop through the columns from column CO up to the last column of values in the last row
[B] If i = firstBlankRow And j = 93 Then[/B]
ElseIf i = lastRow And j > lastCol Then
' do nothing after the last value in the last row
Else
Cells(firstBlankRow, j).Value = Cells(lastRow, j).Value + 1 ' leave the value of each cell in the LastRow + 1
End If
Next j
Next i
End Sub