Counting rows of empty cells between two cells with value

Sweedler · Apr 28, 2023

Hello

I have a rather large spreadsheet, ca 150000 rows. In one column I have values in about 15000 cells, but they are not spread out randomly. I need a method of counting the amount of rows, in that column, that are between two cells with a value. I am not sure that this makes sense:

	This is the result I want - or some version of it	and then this step would be next
30		30
	1	30-1
	2	30-2
26		26
	1	26-1
4		4
	1	4-1
	2	4-2
	3	4-3
60012		60012

I hope this helps basically explain it

DanteAmor · Apr 28, 2023

Hi @Sweedler, thanks for posting on the forum.

I present you a macro, considering the number of rows, I recommend it, since the formula could cause slowness in your sheet.

I assume the data is in column A and you want the results in column B.
If this is not the case, the change in the macro is minimal, you only have to change the "A" by the letter of the column where you have the data. And in the output, change "B2" to the cell where you want the output to start.

That said, put the following macro in a module and execute it.

VBA Code:

Sub CountingRows()
  Dim a As Variant, b As Variant, num As Variant
  Dim i As Long, j As Long
 
  a = Range("A2", Range("A" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 2)
 
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "" Then
      num = a(i, 1)
      b(i, 2) = num
      j = 0
    Else
      j = j + 1
      b(i, 1) = j
      b(i, 2) = num & "-" & j
    End If
  Next
  Range("B2").Resize(UBound(b, 1), 2).Value = b
End Sub

Recommendation: Put columns B and C in text format, since for some results that have hyphen excel it can assume that they are dates.

-------------------------------------------------------------------
But if you prefer the formulas, I propose the following:

Dante Amor

A

B

C

1

This is the result I want - or some version of it

and then this step would be next

2

30

3

4

5

26

6

1

26-1

7

4

8

9

10

11

60012

Hoja2

Cell Formulas
Range	Formula
B2:B11	B2	=IF(A2="",IF(A1<>"",1,B1+1),"")
C2:C11	C2	=IF(B2="",A2,LEFT(C1,FIND("-",C1&"-")-1) &"-"&B2)

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Sweedler · May 5, 2023

DanteAmor said:
Hi @Sweedler, thanks for posting on the forum.

I present you a macro, considering the number of rows, I recommend it, since the formula could cause slowness in your sheet.

I assume the data is in column A and you want the results in column B.
If this is not the case, the change in the macro is minimal, you only have to change the "A" by the letter of the column where you have the data. And in the output, change "B2" to the cell where you want the output to start.

That said, put the following macro in a module and execute it.

VBA Code:

Sub CountingRows() Dim a As Variant, b As Variant, num As Variant Dim i As Long, j As Long a = Range("A2", Range("A" & Rows.Count).End(3)).Value ReDim b(1 To UBound(a, 1), 1 To 2) For i = 1 To UBound(a, 1) If a(i, 1) <> "" Then num = a(i, 1) b(i, 2) = num j = 0 Else j = j + 1 b(i, 1) = j b(i, 2) = num & "-" & j End If Next Range("B2").Resize(UBound(b, 1), 2).Value = b End Sub

Recommendation: Put columns B and C in text format, since for some results that have hyphen excel it can assume that they are dates.

-------------------------------------------------------------------
But if you prefer the formulas, I propose the following:

Dante Amor
A B C
1 This is the result I want - or some version of it and then this step would be next
2 30 30
3 1 30-1
4 2 30-2
5 26 26
6 1 26-1
7 4 4
8 1 4-1
9 2 4-2
10 3 4-3
11 60012 60012
Hoja2
Cell Formulas
Range Formula
B2:B11 B2 =IF(A2="",IF(A1<>"",1,B1+1),"")
C2:C11 C2 =IF(B2="",A2,LEFT(C1,FIND("-",C1&"-")-1) &"-"&B2)

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Thank you sir. I tried both, but settled for the formulas as, for this type of work, I usually prefer them