Enter No of empty rows beside each cell with data

[A Thayuman]

Hi

I would like to know how many empty rows are below each cell with data. Coloum "B" has data - number of empty cells should appear in coloum "A"

I have found this code in one of the forum messages however the code works but I get -

The Microsoft Visual Basic Window pops up - "Rum-time error '6': Overflow

When I hit debug it shows be the error is in "NextName = Range("B" & Counter).End(xlDown).Row"

Sub CountEmpty()

Dim LastRow As Integer
Dim Counter As Integer
Dim NextName As Integer

LastRow = Range("B" & Rows.Count).End(xlUp).Row

For Counter = 2 To LastRow

If Range("B" & Counter).Value = "" Then GoTo NextTry

NextName = Range("B" & Counter).End(xlDown).Row

Range("A" & Counter).Value = NextName - Counter - 1

NextTry:

Next Counter

End Sub

I hope to hear from one of the experts

VBA to add rows.xlsm
	A	B	C	D	E	F	G
1	Empty Cells	Date	Transaction Details	Notes 1	Notes 2	Debit	Credit
2	2	02-Jan-00	Julia Linsyd stay	R4446544180	Transfer Debit	-210.00
3
4
5	5	08-Jan-00	OPTUS BILLING SVC...	INTERNET BPAY	Transfer Debit	-87.20
6
7
8
9
10
11	5	07-Jan-00	ST.GEORGE BANK-C...	1234	Automatic Drawing	-418.70
12
13
14
15
16
17	9	07-Jan-00	HIRE PURCHASE...	0000000237300845	Automatic Drawing	-430.17
18
19
20
21
22
23
24
25
26
27	7	07-Jan-00	LIMIT RATE IS 8.820...		Miscellaneous Credit		0.00
Sheet1

 

[Micron]

Without checking anything else, IIRC that error would be raised if your integer variable exceeds 3,2676 (or whatever the real number is - I forget).
Try Debug.Print Range("B" & Counter).End(xlDown).Row
Looks to me like the value should be over a million because of xlDown.

 

[kweaver]

Similar to @Micron's comment, I assume there must be some way to detect the end of column B.

042523FunctionSyntax.xlsm
	A	B	C	D	E	F	G
1		Date	Transaction Details	Notes 1	Notes 2	Debit	Credit
2	2	2-Jan-00	Julia Linsyd stay	R4446544180	Transfer Debit	-210
3
4
5	5	8-Jan-00	OPTUS BILLING SVC...	INTERNET BPAY	Transfer Debit	-87.2
6
7
8
9
10
11	5	7-Jan-00	ST.GEORGE BANK-C...	1234	Automatic Drawing	-418.7
12
13
14
15
16
17	9	7-Jan-00	HIRE PURCHASE...	0000000237300845		-430.17
18
19
20
21
22
23
24
25
26
27	7	7-Jan-00	LIMIT RATE IS 8.820…		Miscellaneous Credit		0
28
29
30
31
32
33
34
35	0	end
Sheet3

Try:

Sub countblankrows()
Dim lr As Long, n As Long, r As Long
    n = 0
    lr = Cells(Rows.Count, "B").End(xlUp).Row
  For r = lr To 2 Step -1
    If IsEmpty(Cells(r, "B")) Then
      n = n + 1
    Else: Cells(r, "A") = n
      n = 0
    End If
  Next r
End Sub

 

[A Thayuman]

Without checking anything else, IIRC that error would be raised if your integer variable exceeds 3,2676 (or whatever the real number is - I forget).
Try Debug.Print Range("B" & Counter).End(xlDown).Row
Looks to me like the value should be over a million because of xlDown.

Thanks for responding to my query, when I use debug.print in the immediate window I get 27

I also tired 'LastRow = Range("B" & 2000).End(xlUp).Row and got the same error and in debug.print in the immediate window I get 27

is there are better way to write this code

 

[A Thayuman]

Similar to @Micron's comment, I assume there must be some way to detect the end of column B.

042523FunctionSyntax.xlsm
	A	B	C	D	E	F	G
1		Date	Transaction Details	Notes 1	Notes 2	Debit	Credit
2	2	2-Jan-00	Julia Linsyd stay	R4446544180	Transfer Debit	-210
3
4
5	5	8-Jan-00	OPTUS BILLING SVC...	INTERNET BPAY	Transfer Debit	-87.2
6
7
8
9
10
11	5	7-Jan-00	ST.GEORGE BANK-C...	1234	Automatic Drawing	-418.7
12
13
14
15
16
17	9	7-Jan-00	HIRE PURCHASE...	0000000237300845		-430.17
18
19
20
21
22
23
24
25
26
27	7	7-Jan-00	LIMIT RATE IS 8.820…		Miscellaneous Credit		0
28
29
30
31
32
33
34
35	0	end
Sheet3

Try:

Sub countblankrows()
Dim lr As Long, n As Long, r As Long
    n = 0
    lr = Cells(Rows.Count, "B").End(xlUp).Row
  For r = lr To 2 Step -1
    If IsEmpty(Cells(r, "B")) Then
      n = n + 1
    Else: Cells(r, "A") = n
      n = 0
    End If
  Next r
End Sub

Thanks kweaver, works like a dream your help is very much appreciated, really well done​

 

[kweaver]

Happy to hear it worked.

 

[Micron]

I also tired 'LastRow = Range("B" & 2000).End(xlUp).Row and got the same error and in debug.print in the immediate window I get 27

Sure, on one particular iteration I guess. This is what I get
5
11
17
27
1048576

 

[A Thayuman]

Sure, on one particular iteration I guess. This is what I get
5
11
17
27
1048576

Thanks Micron for your response, I know this is a problem but dont know enough to take it any further, at least you have pointed me as to why there is an overflow error with end (xldown), thank you again

 

[Micron]

NP. To fix the overflow problem you'd use a variable type that will accept the number you intend to put in it. In this case, a Long - good for over 2 billion. That's why you got the overflow error - last row number exceeds 1 million. Integer is good for +-32,767
What I meant in my first post is that you should use xlUp to find the last row, not xlDown, which is what you were given in post 3:

lr = Cells(Rows.Count, "B").End(xlUp).Row