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

Answer:

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
                MsgBox intervalo(i, j).Address
                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.

Post Views: 0
Scroll to Top