Find last filled cell in a range

Asked

Viewed 8,865 times

3

It is possible to find the last filled cell in a range and paste a value into the next empty line below?

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

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
excel vba

1 answer

2

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 A10 to A30 range 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 that 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 the Spreadsheet

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

Upshot:

Then the line in 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

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

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.