# excel – Find last filled cell in a range

## Question:

Is it possible to find the last filled cell in a `range` and paste a value into the next empty row below?

I need a code that searches the range `("B5:B35")` . I used the following code, but after cell 35 I have other information so the data is pasted in the wrong place

``````If Not Application.Intersect(Target, Range("B4:B8,B11:B15")) Is
Nothing Then Range(Cells(Selection.Row, 2), Cells(Selection.Row,
4)).Select Selection.Copy

Worksheets("Cálculos").Cells(Rows.Count, 2).End(xlUp).Offset(1,
0).PasteSpecial xlPasteValues
``````

### Last cell of a range

``````    Dim Calc As Worksheet
Dim intervalo As Range
Dim matriz As Variant
Dim i As Long, j As Long
Set Calc = ThisWorkbook.Worksheets("Cálculos")
Set intervalo = Calc.Range("A20:C30")
matriz = intervalo

For j = intervalo.Columns.Count To 1 Step -1
For i = intervalo.Rows.Count To 1 Step -1
If matriz(i, j) <> "" Then
GoTo sairLoop
End If
Next i
Next j
sairLoop:
``````

### Last Row of a Range in a Column

For example from the range A10 to A30 if it is filled only up to A26, it will return 26:

``````Dim Calc As Worksheet
Dim UltimaLinha As Long
Set Calc = ThisWorkbook.Worksheets("Cálculos")

With Calc.Range("A10:A30").CurrentRegion
UltimaLinha = .Rows(.Rows.Count).Row
End With
``````

### Last Row of a Column

To find the last line, there are several ways, but the one I use the most is:

`UltimaLinha= Worksheets("Cálculos").Cells(Worksheets("Cálculos").Rows.Count, "B").End(xlUp).Row`

or

``````Dim Calc As Worksheet
Set Calc = ThisWorkbook.Worksheets("Cálculos")

With Calc
UltimaLinha= .Cells(.Rows.Count, "B").End(xlUp).Row
End With
``````

Where

``````Dim Calc As Worksheet
Set Calc = ThisWorkbook.Worksheets("Cálculos")
UltimaLinha= Calc.Cells(Calc.Rows.Count, "B").End(xlUp).Row
``````

### Last Line of the Worksheet

``````Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Planilha1")
lngLstRow = ws.UsedRange.Rows.Count
``````

### Result:

So the line of your code would change to:

`Worksheets("Cálculos").Cells(UltimaLinha, 2).Offset(1, 0).PasteSpecial xlPasteValues`

or

`Worksheets("Cálculos").Cells(UltimaLinha + 1, 2).PasteSpecial xlPasteValues`

Note: Declare the LastLine as Long ( `Dim UltimaLinha As Long` ), as many old tutorials use Integer, which has 2 bytes and the range from –32 768 to 32 767. Therefore, if the Excel version is greater than 2007, the program will stop after line 32767. Long has 4 bytes and range from -2 147 483 648 to 2 147 486 647. Where Excel has a limit of 1 048 576 lines.

Scroll to Top