Count nonblank lines (between blank lines)

H

harzer

Board Regular
Joined
Dec 15, 2021
Messages
122
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone,
As I can't find a solution to my problem, I present my request hoping for a solution from you with a code in vba (No Formulas please).
In the attachment, I want to count the number of row with data (starting from the 2nd row) until the moment we encounter the first empty row, then we place this number found in the last non-empty row in column "L", unless I am mistaken, I placed the result in the data table in column "L".
Then we continue counting for the rest of the table.
I put a small part of the data from my sheet because my data is important, that's why I allow myself to ask you for a rather quick solution like the use of tables.
I remain at your disposal for any additional information.
Cheers.

Here is the illustration of my dat

DeterminerSexe.xlsm
ABCDEFGHIJKL
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursInformationElevageCompteur
2AE27-093/2012 MMA76-020/2011 MMM72-112/2011 FGérard Claude10a 11m 28j2B1608-06-124TFemelle
3AE27-094/2012 MMA76-020/2011 MMM72-112/2011 FGérard Claude10a 11m 28j2B1608-06-124TFemelle
4AE27-095/2012 FMA76-020/2011 MMM72-112/2011 FGérard Claude10a 11m 28j2B1608-06-124TFemelle
5AE27-096/2012 FCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j2B1310-06-124TFemelle
6AE27-097/2012 MCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j3H1310-06-124TMâle
7AE27-098/2012 MCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j3H1310-06-124TMâle
8AE27-099/2012 MCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j3H1310-06-124TMâle
9AE27-100/2012 FCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j2B1310-06-124TFemelle 8
10
11AE27-053/2013 MAE27-098/2012 MAE27-038/2012 FGérard Claude9a 11m 3j4H301-07-134TFemelle
12AE27-054/2013 MPG17-002/2012 MPG15-118/2012 FGérard Claude10a 1m 0j5H1004-05-134TFemelle
13AE27-055/2013 FPG17-002/2012 MPG15-118/2012 FGérard Claude10a 1m 0j5H1004-05-134TMâle
14AE27-056/2013 FMM72-076/2012 MPG14-041/2012 FGérard Claude9a 11m 11j5H2225-06-134TMâle
15AE27-057/2013 FCF03-156/2011 MMM72-114/2011 FGérard Claude10a 1m 0j2B1304-05-134TFemelle
16AE27-058/2013 MPG14-029/2012 MPG14-031/2012 FGérard Claude10a 1m 11j5H1825-04-134TMâle
17AE27-059/2013 FCF03-156/2011 MMM72-114/2011 FGérard Claude10a 1m 11j3H1325-04-134TMâle
18AE27-060/2013 MPG14-029/2012 MPG14-050/2012 FGérard Claude10a 0m 31j5H1905-05-135TMâle
19AE27-062/2013 MPG14-029/2012 MPG14-050/2012 FGérard Claude10a 0m 17j5H1919-05-134TMâle 9
20
21AE27-063/2013 MPG14-029/2012 MPG14-050/2012 FGérard Claude10a 0m 30j5H1906-05-135TMâle
22AE27-064/2013 FPG14-029/2012 MPG14-050/2012 FGérard Claude10a 1m 16j5H1920-04-134TMâle
23AE27-065/2013 MAE27-076/2012 MAE27-087/2012 FGérard Claude10a 0m 30j4H1506-05-135TMâle
24AE27-066/2013 MAE27-076/2012 MAE27-087/2012 FGérard Claude10a 0m 30j4H1506-05-135TMâle
25AE27-067/2013 MAE27-076/2012 MAE27-087/2012 FGérard Claude10a 0m 30j4H1506-05-135TMâle
26AE27-068/2013 FAE27-076/2012 MAE27-087/2012 FGérard Claude10a 0m 30j1B1506-05-135TFemelle
27AE27-069/2013 FPG14-029/2012 MPG14-031/2012 FGérard Claude10a 1m 0j2B1804-05-134TFemelle
28AE27-070/2013 MYM856-028/2010 MAE27-044/2012 FGérard Claude10a 1m 0j3H204-05-134TMâle
29AE27-071/2013 FYM856-028/2010 MAE27-044/2012 FGérard Claude10a 1m 0j3B204-05-134TFemelle
30AE27-072/2013 MYM856-028/2010 MAE27-044/2012 FGérard Claude9a 11m 24j3H212-06-135TMâle
31AE27-073/2013 FYM856-028/2010 MAE27-044/2012 FGérard Claude10a 0m 23j3H213-05-134TMâle
32AE27-074/2013 MYM856-028/2010 MAE27-044/2012 FGérard Claude10a 0m 23j3H213-05-134TMâle
33AE27-075/2013 MYM856-028/2010 MAE27-044/2012 FGérard Claude10a 0m 22j5H214-05-134TMâle 13
34
35AE27-077/2013 FAE27-078/2012 MYM856-083/2012 FGérard Claude10a 0m 23j3H113-05-134TMâle
36AE27-078R-12/2013 MCF03-156/2011 MAE27-044/2012 FGérard Claude10a 0m 23j5H213-05-134TMâle
37AE27-078/2013 MAE27-078/2012 MYM856-083/2012 FGérard Claude10a 0m 23j3H113-05-134TMâle
38AE27-079/2013 FAE27-078/2012 MYM856-083/2012 FGérard Claude10a 0m 17j3H119-05-134TMâle
39AE27-080/2013 FAE27-086/2012 MMN96-075/2012 FGérard Claude10a 0m 17j5B1619-05-134TFemelle
40AE27-081/2013 MAE27-086/2012 MMN96-075/2012 FGérard Claude9a 11m 11j3H1625-06-134TMâle
41AE27-082/2013 FAE27-086/2012 MMN96-075/2012 FGérard Claude10a 0m 17j3H1619-05-134TMâle
42AE27-083R-12/2013 FYM856-117/2009 MAE27-056/2012 FGérard Claude10a 0m 18j2B1218-05-134TFemelle
43AE27-083/2013 FAE27-086/2012 MMN96-075/2012 FGérard Claude10a 0m 18j2B1618-05-134TFemelle
44AE27-084R-10/2013 MYM856-117/2009 MYM856-083/2012 FGérard Claude10a 0m 18j3H118-05-134TMâle
45AE27-084/2013 MAE27-086/2012 MMN96-075/2012 FGérard Claude10a 0m 17j3H1619-05-134TMâle
46AE27-085R-12/2013 MYM856-101/2012 MMN96-075/2012 FGérard Claude9a 11m 26j3H1610-06-134TMâle
47AE27-085/2013 MAE27-032/2012 MPG15-131/2012 FGérard Claude10a 0m 18j5H2118-05-134TMâle
48AE27-086/2013 FAE27-032/2012 MPG15-131/2012 FGérard Claude9a 11m 9j2B2127-06-134TFemelle
49AE27-087/2013 FYM856-028/2010 MPG14-041/2012 FGérard Claude10a 0m 18j5H2218-05-134TMâle
50AE27-088/2013 MYM856-028/2010 MPG14-041/2012 FGérard Claude9a 11m 3j2H2201-07-134TMâle
51AE27-089/2013 FYM856-028/2010 MPG14-041/2012 FGérard Claude10a 0m 18j5H2218-05-135TMâle
52AE27-090/2013 MCF03-156/2011 MYM856-131/2011 FGérard Claude10a 0m 18j5H1418-05-135TMâle
53AE27-091/2013 MCF03-156/2011 MYM856-131/2011 FGérard Claude10a 0m 0j4H1404-06-135TMâle
54AE27-092/2013 FCF03-156/2011 MYM856-131/2011 FGérard Claude10a 0m 0j5H1404-06-135TMâle
55AE27-093/2013 FCF03-156/2011 MYM856-131/2011 FGérard Claude10a 0m 0j5H1404-06-135TMâle 21
56
57AE27-095/2013 FCF03-156/2011 MYM856-131/2011 FGérard Claude10a 0m 0j5H1404-06-134TMâle
58AE27-096/2013 MPG14-011/2012 MPG14-020/2012 FGérard Claude10a 0m 0j5H1704-06-134TMâle
59AE27-097/2013 FPG14-011/2012 MPG14-020/2012 FGérard Claude10a 0m 0j5H1704-06-134TMâle
60AE27-098/2013 FPG14-011/2012 MPG14-020/2012 FGérard Claude9a 11m 31j2B1705-06-134TFemelle
61AE27-099/2013 MCF03-156/2011 MAE27-044/2012 FGérard Claude10a 0m 0j3H204-06-134TMâle
62AE27-100/2013 FPG15-121/2012 MPG14-050/2012 FGérard Claude9a 11m 24j5H1912-06-135TFemelle
63AE27-168-11/2013 FYM856-028/2010 MMM72-112/2011 FGérard Claude9a 0m 2j2B802-06-145TFemelle
64AE27-169-11/2013 MYM856-028/2010 MMM72-112/2011 FGérard Claude9a 11m 3j5H801-07-134TMâle
65AE27-170-11/2013 MYM856-028/2010 MMM72-112/2011 FGérard Claude9a 11m 9j3H827-06-134TMâle
66AE27-171-11/2013 FYM856-028/2010 MMM72-112/2011 FGérard Claude9a 11m 9j2B827-06-134TFemelle
67AE27-173-11/2013 FAE27-027/2011 MYM856-053/2011 FGérard Claude10a 0m 12j2B2024-05-134TFemelle
68AE27-174R-11/2013 MYM856-117/2009 MYM856-083/2012 FGérard Claude10a 0m 12j5H124-05-134TMâle
69AE27-175-11/2013 FAE27-027/2011 MYM856-053/2011 FGérard Claude10a 0m 12j2B2024-05-134TFemelle
70AE27-001/2014 FPG14-017/2013 MPG14-050/2012 FGérard Claude9a 1m 25j5H2211-04-145TFemelle
71AE27-002/2014 MPG14-017/2013 MPG14-050/2012 FGérard Claude9a 1m 25j5H2211-04-145TFemelle
72AE27-020/2014 MWH1183-037/2012 MPG14-020/2012 FGérard Claude9a 1m 21j5H2015-04-145TFemelle
73AE27-021/2014 MWH1183-037/2012 MPG14-020/2012 FGérard Claude9a 1m 21j5H2015-04-145TFemelle
74AE27-022/2014 MWH1183-037/2012 MPG14-020/2012 FGérard Claude9a 1m 21j5H2015-04-145TFemelle
75AE27-023/2014 FAE27-064/2013 MPG15-102/2013 FGérard Claude9a 1m 21j5H1815-04-145TMâle
76AE27-001/2019 MPG14-001/2016 MAE27-010/2018 FGérard Claude4a 1m 17j5H1219-04-195TFemelle
77AE27-002/2019 mPG14-001/2016 MAE27-010/2018 FGérard Claude4a 1m 17j5H1219-04-195TFemelle 21
78
79AE27-006/2019 FPG14-003/2018 MAE27-075/2017 FGérard Claude4a 1m 17j5H1019-04-195TFemelle Tt jaune
80AE27-007/2019 MPG14-003/2018 MAE27-075/2017 FGérard Claude4a 1m 17j5H1019-04-195TFemelle Tt jaune
81AE27-008/2019 MPG14-003/2018 MAE27-075/2017 FGérard Claude4a 1m 17j5H1019-04-195TFemelle Tâche au dos
82AE27-009/2019 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j5H1122-04-195TFemelle Tte jaune
83AE27-010/2019 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j5H1122-04-195TFemelle Tt jaune
84AE27-011/2019 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j4H1122-04-195TFemelle Tte jaune
85AE27-012/2019 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j5H1122-04-195TFemelle Tâche derrière la tête
86AE27-013/2019 MPG14-048/2016 MAE27-008/2018 FGérard Claude4a 1m 14j4H122-04-194TMâle Tt jaune
87AE27-014/2019 FAE27-029/2017 MPG14-048/2018 FGérard Claude4a 1m 16j4H220-04-194TFemelle Tte jaune
88AE27-015/2019 MAE27-032/2017 MAE27-007/2018 FGérard Claude4a 1m 17j4H619-04-194TMâle
89AE27-017/2019 FAE27-032/2017 MAE27-007/2018 FGérard Claude4a 1m 17j3H619-04-194TFemelle Tt jaune - Ptes Flûtes - cris d'appel!
90AE27-018/2019 MAE27-032/2017 MAE27-007/2018 FGérard Claude4a 1m 17j4H619-04-194TMâle
91AE27-019/2019 MAE27-034/2017 MAE27-034/2018 FGérard Claude4a 1m 16j4H820-04-194TMâle Tâche tête
92AE27-020/2019 MAE27-034/2017 MAE27-034/2018 FGérard Claude4a 1m 16j4H820-04-194TMâle Tâche derrière œil et tête - Klingelrol
93AE27-035/2022 MMN96-005/2021 MAE27-068/2021 FGérard Claude0a 11m 23j5H413-06-224TMâle Tt jaunex
94AE27-036/2022 MMN96-005/2021 MAE27-068/2021 FGérard Claude0a 11m 23j5H413-06-224TMâle Tt Jaune
95AE27-037/2022 FMN96-005/2021 MAE27-068/2021 FGérard Claude0a 11m 23j4H413-06-224TFemelle Tte jaunex
96AE27-038/2022 FMN96-005/2021 MAE27-068/2021 FGérard Claude0a 11m 23j4H413-06-224TFemelle avec tâche derrière la têtex
97AE27-039/2022 FAE27-049/2021 MAE27-003/2021 FGérard Claude0a 11m 23j5H113-06-224TFemelle tâche ronde derrière la têtex
98AE27-040/2022 FAE27-049/2021 MAE27-003/2021 FGérard Claude0a 11m 23j4H113-06-224TFemelle Tte jaune
99AE27-041/2022 FMN96-046/2019 MMN96-036/2021 FGérard Claude0a 11m 18j4H618-06-224TFemelle Tte jaune
100AE27-042/2022 MMN96-046/2019 MMN96-036/2021 FGérard Claude0a 11m 18j5H618-06-224TMâle Tt jaunex
101AE27-043/2022 FMN96-046/2019 MMN96-036/2021 FGérard Claude0a 11m 18j4H618-06-224TFemelle Tte jaune23
102
103AE27-045/2022 MAE27-024/2021 MAE27-016+/2020 FGérard Claude0a 11m 11j5H825-06-224TMâle Tâche aile droite (n'aime pas être pris en main)
104AE27-046/2022 MAE27-024/2021 MAE27-016+/2020 FGérard Claude0a 11m 11j5H825-06-224TMâle Tte jaune
105AE27-047/2022 FAE27-024/2021 MAE27-016+/2020 FGérard Claude0a 11m 11j4H825-06-224TFemelle Tte jaune
106AE27-048/2022 FAE27-006/2021 MAE27-052/2021 FGérard Claude0a 11m 8j4H328-06-224TFemelle Tâche aux 2 ailes
107AE27-049/2022 FAE27-006/2021 MAE27-052/2021 FGérard Claude0a 11m 8j4H328-06-224TFemelle Tâche ronde der, Tête + tâche aile droite
108AE27-001/2023 MAE27-008/2022 MAE27-039/2022 FGérard Claude0a 1m 11j5H1525-04-234TMâle Tache au dos - Mange seul à 26 jours
109AE27-002/2023 MAE27-035/2022 MMN96-008/2022 FGérard Claude0a 1m 3j5H1101-05-234TMâle T Jaune (Pas Sauvage)
110AE27-003/2023 FAE27-032/2022 MAE27-026/2022 FGérard Claude0a 0m 31j5H1205-05-234TFemelle tâche tête
111AE27-004/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-234TMâle Tâche tête
112AE27-005/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-234TMâle
113AE27-006/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-234TMâle
114AE27-007/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-234TMâle
115AE27-008/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-234TMâle
116AE27-009/2023 MAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j5H622-05-234TMâle
117AE27-010/2023 FAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j5H622-05-234TMâle
118AE27-011/2023 MAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j5H622-05-234TMâle
119AE27-012/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-234TMâle
120AE27-013/2023 FAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-234TMâle
121AE27-014/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-234TMâle
122AE27-018/2023 MAE27-33/2022 MAE27-024/2022 FGérard Claude0a 0m 11j5H725-05-234TMâle 20
Feuil1
 
Sort by date Sort by votes
sofas said:
Hello.I want to use the same code for pagination, meaning that the calculation of the number of rows is changed to write page 1, page 2
Click to expand...
Working with pages is quite different to this thread. Please start a new thread.
In the new thread you will need to provide details on whether you have hard page breaks set up or not and if the code is to set them up how it is to determine where to put them.
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Alex Blakenburg said:
Nice @mohadin. If the OP is happy with just checking for blank in just 1 column as appears to be the case in both the previous solutions then a using an array is still faster.
(although it is by a factor of 10 it is still barely noticeable since they are both a fraction of a second with Mohadin's code coming in at .164 secs vs the array approach at .023 secs over 10k records)

VBA Code: 
Sub CountNonEmptyRow_SingleColumnChk()

    Dim sht As Worksheet
    Dim lrow As Long
    Dim rngMain As Range, rngCount As Range
    Dim arrMain As Variant, arrCount() As Variant
    Dim i As Long, iCnt As Long
   
    Set sht = Worksheets("Feuil 1")
    With sht
        lrow = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngMain = .Range("A2:L" & lrow + 1)         ' Include 1 extra row to simplify the loop
        arrMain = rngMain.Value
        Set rngCount = .Range("L2")
    End With
   
    ReDim arrCount(1 To UBound(arrMain, 1), 1 To 1)
   
    For i = 1 To UBound(arrMain)
        If arrMain(i, 1) <> "" Then
            iCnt = iCnt + 1
        Else
            arrCount(i - 1, 1) = iCnt
            iCnt = 0
        End If
    Next i
   
    rngCount.Resize(UBound(arrMain), 1).Value = arrCount
End Sub
Click to expand...
Hello.I want to use the same code for pagination, meaning that the calculation of the number of rows is changed to write page 1, page 2
 
Upvote 0
sofas said:
Hello.I want to use the same code for pagination, meaning that the calculation of the number of rows is changed to write page 1, page 2
Click to expand...
Give this a try:

VBA Code: 
Sub PagesNoAtLastRowInSection()

    Dim sht As Worksheet
    Dim lrow As Long
    Dim rngMain As Range, rngCount As Range
    Dim arrMain As Variant, arrCount() As Variant
    Dim i As Long, iCnt As Long
    
    Set sht = Worksheets("Feuil 1")
    With sht
        lrow = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngMain = .Range("A2:L" & lrow + 1)         ' Include 1 extra row to simplify the loop
        arrMain = rngMain.Value
        Set rngCount = .Range("L2")
    End With
    
    ReDim arrCount(1 To UBound(arrMain, 1), 1 To 1)
    
    For i = 1 To UBound(arrMain)
        If arrMain(i, 1) = "" Then
            iCnt = iCnt + 1
            arrCount(i - 1, 1) = "page " & iCnt
        End If
    Next i
    
    rngCount.Resize(UBound(arrMain), 1).Value = arrCount
End Sub
 
Upvote 1
Alex Blakenburg said:
Give this a try:

VBA Code: 
Sub PagesNoAtLastRowInSection()

    Dim sht As Worksheet
    Dim lrow As Long
    Dim rngMain As Range, rngCount As Range
    Dim arrMain As Variant, arrCount() As Variant
    Dim i As Long, iCnt As Long
   
    Set sht = Worksheets("Feuil 1")
    With sht
        lrow = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngMain = .Range("A2:L" & lrow + 1)         ' Include 1 extra row to simplify the loop
        arrMain = rngMain.Value
        Set rngCount = .Range("L2")
    End With
   
    ReDim arrCount(1 To UBound(arrMain, 1), 1 To 1)
   
    For i = 1 To UBound(arrMain)
        If arrMain(i, 1) = "" Then
            iCnt = iCnt + 1
            arrCount(i - 1, 1) = "page " & iCnt
        End If
    Next i
   
    rngCount.Resize(UBound(arrMain), 1).Value = arrCount
End Sub
Click to expand...
Always brilliant. Thank you very much. This is what is really needed
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Modify content of cells in column "F" according to the content of cells in column "A"
Replies
5
Views
342
igold
igold
H
  • Solved
Insert a blank row when the cell in column "B" changes.
2
Replies
10
Views
711
Peter_SSs
Peter_SSs
H
  • Solved
Find Value of a cell in column "B"
Replies
4
Views
313
Akuini
Akuini
H
  • Question
Insert the space character in the penultimate place of all the cells of columns "A" - "B" & "C".
Replies
3
Views
233
Fluff
Fluff
H
  • Solved
If the hyphen character (-) does not exist, it is inserted in a specific place.
Replies
6
Views
390
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,077
Messages
6,122,992
Members
449,094
Latest member
masterms

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top