2

I have a question regarding how to count cells in excel using vba, I've found this code but is not working as intended

Dim count As Long
count= ActiveSheet.Range("B5", ActiveSheet.Cells(Rows.count,1).End(xlDown)).Rows.count
MsgBox count

My problem is, lets say my data header is in cell B4 and my data starts starts from B5 to an unknown row in the same B column. How could I count how many rows are filled (starting from B5) ? (there are no blank cells)

I also have found how to assign the values from the range to the array as

 MyVector = Range("B5:B" & count).Value

But I don't know how to know the count variable

Any help would be much appreciated

Improve this question
0

3 Answers 3

Reset to default
2

Edited: for proper range to Array sizing

  • data starts from B5

  • there are no blank cells

then use this

Dim count As Long
count = WorksheetFunction.CountA(Range("B5", Range("B5").End(xlDown)))

Dim MyVector As Variant
MyVector = Range("B5").Resize(count).Value

or, directly:

Dim MyVector As Variant
MyVector = Range("B5").Resize(WorksheetFunction.CountA(Range("B5", Range("B5").End(xlDown)))).Value

However, if there can be no data at all, then check count before filling the array:

Dim count As Long
count = WorksheetFunction.CountA(Range("B5", Range("B5").End(xlDown)))

Dim MyVector As Variant
If count > 0 Then MyVector = Range("B5").Resize(count).Value
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Be careful because if no data after B5 that will go to the end of the sheet.
@SJR, the OP said "there are no blank cells" and in any case I also gave code for "if there can be no data at all" scenario. So I've been careful
2

Use column B (not 1="A") and work up from the bottom just in case you only have one entry (you are trying to go down from the very last row in the sheet).

Dim count As Long
count= ActiveSheet.Range("B5", ActiveSheet.Cells(Rows.count,"B").End(xlUp)).count
MsgBox count
MyVector = Range("B5").Resize(count).Value
Improve this answer

1 Comment

@ScottCraner - thanks, I think my edit using Resize will take care of that (of course one could dispense with the variable altogether).
2

You could also use:

Dim count As Long
count= ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp)).Row - 5 + 1
MsgBox count

MyVector = Range("B5:B" & count + 4).Value
Improve this answer

5 Comments

Wait, if the OP wants to use count in MyVector = Range("B5:B" & count).Value then we want the actual row and not the count of the range or the ending will be 5 off.
@ScottCraner What about now?
Why do the math then undo the math? Just remove the -5+1 and the +4
^ yup over thought this one, you have the beginning and the end to define the range.
@ScottCraner Because I thought that maybe the OP still wants to know the number of cells with values after B5, that is why he used count. If he wants to know the value, he can just call count whenever he wants.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.