CODEWITHSUNDEEP
excelvba
Swi
Swi

Reputation: 125

Find last row in a column with data and paste other data below and repeat

I found the below code from Here.

With Sheets("Sheet1")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
    lastrow = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).Row
Else
    lastrow = 1
End If
End With

My reputation is too low so I can't comment and ask there. What or where is the output? I'm not able to bind the code in?

Upvotes: 1

Views: 1708

Answers (1)

user4039065
user4039065

Reputation:

That code gives you the last row of any column within the worksheet's Worksheet.UsedRange property with a value in it; not the last cell with a value in one particular column. The two may be the same thing but are not guaranteed to be the same.

To get the row with the last value in a particular column (e.g. column B) then this would be more appropriate.

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Columns(2)) <> 0 Then
        lastrow = .Cells(rows.Count, "B").End(xlUp).Row
    Else
        lastrow = 1
    End If
End With

To use this lastrow to set a value in the next cell (first blank), add 1 and use it in the row_num parameter of a Range.Cells property.

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Columns(2)) <> 0 Then
        lastrow = .Cells(rows.Count, "B").End(xlUp).Row
    Else
        lastrow = 1
    End If
    .Cells(lastrow + 1, "B") = "my new value"
End With

Upvotes: 3

Related Questions