CODEWITHSUNDEEP
vbaexcel
H.hisoka
H.hisoka

Reputation: 61

Get the number of next empty rows

I want to get the number of the next empty rows, calculated from a declared cell : As you can see this is the problem : get N° 7 declared cell is "A4"

I do this but I don't get the correct response : VBA Code1 

Sub sup_rapp1()
'*variables
Dim CELDEB As Range, LIGNEFIN As Long, COLONEFIN As Long, WS As Worksheet
'set objet : affecter les objets
Set WS = Sheets("TAB1")
Set CELDEB = Range("A4")
'derniere ligne at colonne of data
LIGNEFIN = WS.Cells(WS.Rows.Count, CELDEB.Column).End(xlUp).Row
MsgBox LIGNEFIN

wronge reponse Code 1

Code2

Sub sup_rapp1()
'*variables
Dim CELDEB As Range, LIGNEFIN As Long, COLONEFIN As Long, WS As Worksheet
'set objet : affecter les objets
Set WS = Sheets("TAB1")
Set CELDEB = Range("A4")
'derniere ligne at colonne of data
LIGNEFIN = WS.Cells(WS.Rows.Count, CELDEB.Column).End(xlDown).Row
MsgBox LIGNEFIN

wrong reponse code 2

Upvotes: 0

Views: 294

Answers (1)

ib11
ib11

Reputation: 2568

I think this is what you are looking for:

Set WS = Sheets("TAB1")
Set CELDEB = WS.Range("A4")

LIGNEFIN = CELDEB.CurrentRegion.Row + CELDEB.CurrentRegion.Rows.Count

MsgBox CStr(LIGNEFIN)

The CurrentRegion gives you the boundaries of the region your cell is in.

The reason you did not get the correct result with your code was that you selected the last row on the sheet and then went up, which gave you 11 or when down (that is you stayed on the last row) which gave you the other value.

Upvotes: 1

Related Questions