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 Line of a Range in a Column
For example the range from A10 to A30 if it is only filled 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 most use 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
or
Dim Calc As Worksheet
Set Calc = ThisWorkbook.Worksheets("Cálculos")
UltimaLinha= Calc.Cells(Calc.Rows.Count, "B").End(xlUp).Row
Last Line of Spreadsheet
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Planilha1")
lngLstRow = ws.UsedRange.Rows.Count
Result:
Then 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 Last Row as Long ( Dim UltimaLinha As Long ), since many older tutorials use Integer, which has 2 bytes and
the range of -32 768 to 32 767. Therefore, if the version of Excel is
higher than 2007, the program will stop after line 32767. Already the Long
has 4 bytes and range from -2 147 483 648 to 2 147 486 647. In which the
Excel has a limit of 1 048 576 lines.
