0

all three lines of code use the same type of syntax. if i am not mistaken, the each of the code should return number of rows from header until just before a blank cell.

Expected result from the image: cntr2 : 1 cntr3 : 1 cntr : 3

Actual result: cntr2 : 8 cntr3 : 2 cntr : 3

here are the codes:

Sub mengelear()

    cntr2 = Range("K10", Range("K10").End(xlDown)).Count
    cntr3 = Range("AC10", Range("AC10").End(xlDown)).Count
    cntr = Range("U10", Range("U10").End(xlDown)).Count

    MsgBox (cntr)
 

End Sub

enter image description here

how do i solve this. tried the syntax "Clear" and "Clearcontent" but the result is still the same.

Your help and advices are really much needed and appreciated. Thanks in advance

Improve this question
5
  • 1
    Range.count returns how many cells contains the range. without checking if the cell is empty or not. If you need to count only non blank cells, probably you need to use functions like COUNTA or COUNTIF Commented Dec 1, 2022 at 9:37
  • 1
    After trying out different methods from several posts includin stackoverflow.com/questions/29639474/… which I thought would do it, it was in fact stackoverflow.com/questions/58000266/… that got me to CountA but then @FoxfireAndBurnsAndBurns already got it for you. Application.CountA(Range("K10", Range("K10").End(xlDown))) should do it. Commented Dec 1, 2022 at 9:46
  • Does this answer your question? Counting Excel Table Rows that are populated with data on VBA Commented Dec 1, 2022 at 11:34
  • @foxfireandburnsandburns thanks for the help and also the link attatched. Problem solved Commented Dec 2, 2022 at 1:20
  • @notus_panda yeah, that should really do the works. Before this i thought function counta and countblank are exclusive to excel. But thanks to you, by adding application. i can use counta and countif Commented Dec 2, 2022 at 1:21
Related questions
2
VBA xlCount function
0
macro to count and give result
0
calculate count of certain cells using vba in excel
Load 7 more related questions Show fewer related questions

0

Reset to default

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.