Reputation: 1272
My records start in cell B8, and I need to count them in Column A, starting in cell A8.
I used the answer from this question "autofill down according to adjacent column" but how do I handle when the recordcount is 0 or 1? When there is 1 record, it errors. Seems Autofill can't autofill one cell.
I have some conditions testing for zero and one, but is there a cleaner way to do it?
LastRow = Cells(Rows.Count, 2).End(xlUp).Row 'Find last row
If LastRow >= 8 Then 'If recordcount is 1 or greater
Range("A8").FormulaR1C1 = "1" 'First number is "1"
If Not LastRow = 8 Then 'If not recordcount exactly 1
'Enumerate the remaining records with Autofill
Range("A8").AutoFill Destination:=Range("A8:A" & LastRow), Type:=xlLinearTrend
End If
End If
Upvotes: 0
Views: 1684
Reputation: 2477
This has two tested methods to accomplish what you are trying to do. The first method is what you were already using, removing the elements that were preventing it from working. The second is another method to examine, because it might give you ideas for a future project that has similar requirements but using autofill isn't what is required.
TESTED
Sub AutoFiller()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).row 'Find last row
Sheets("Sheet1").Cells(8, 1) = 1 'First number is "1"
Range("A8").AutoFill Destination:=Range("A8:A" & LastRow), Type:=xlLinearTrend
End Sub
Using LOOP instead of Autofill:
Sub VBAAutoFill()
Dim LastRow As Long
Dim count As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
count = 1
For lRow = 8 to LastRow
Sheets("Sheet1").Cells(lRow, 1) = count
count = count + 1
Next lRow
end Sub
EXCEL FUNCTION: count all your entries, if you just want to know how many there are total..
COUNTA(B8:B10000)
edit notes: elaborating on process in notes, correcting typos. Original edit added solutions.
Upvotes: 1
Reputation: 3177
My records start in cell B8, and I need to count them in Column A, starting in cell A8.
I'm taking this to mean that, you are essentially enumerating the rows. This should be helpful to resolve your error issues, and if it's not exactly what you want in the A
column, it shouldn't be hard for you to make an adjustment:
Public Sub GetRangeCount()
Dim bRange As Range
Set bRange = Range(Range("B8"), Range("B1048575").End(xlUp))
Dim i As Integer
i = 0
For Each c In bRange
If c.Value <> "" Then
i = i + 1
c.Offset(0, -1).Value = i
End If
Next
End Sub
Upvotes: 1