Get the number of next empty rows

Question

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

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

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

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

Possible duplicate of [Select first empty cell in column F starting from row 1. (without using offset )](http://stackoverflow.com/questions/14957994/select-first-empty-cell-in-column-f-starting-from-row-1-without-using-offset) — timbmg, May 20 '16 at 07:11

score 1 · Accepted Answer · answered May 20 '16 at 07:24

1

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.

answered May 20 '16 at 07:24

ib11

2,530
3
22
55

thank you it's work correctly ; i didn't understand the last row of your code : "CStr ("LIGNEFIN"). ? – H.hisoka May 20 '16 at 07:39
1

@H.hisoka, CSTR is nothing but conversion of value to string – Karthick Gunasekaran May 20 '16 at 07:44

Get the number of next empty rows

1 Answers1