CODEWITHSUNDEEP
excelvba
Ritu Mishra
Ritu Mishra

Reputation: 83

How to copy a column from a specific row all the way down to the last row with values?

I have two columns.. "AE" and "AG"

The headers are in row 4 of the worksheet and all the data starts from row 5 onwards.

I just want to copy the whole column of "AG" starting from "AG5" until the first blank row and paste it AS values into "AE" in the exact same range (starting from "AE5"). How can I go about doing that?

Upvotes: 0

Views: 136

Answers (2)

99moorem
99moorem

Reputation: 1983

Try below.

With ActiveSheet
    LR = .Range("AG1048576").End(xlUp).Row

    .Range("AG5:AG" & LR).Copy
    .Range("AE5").PasteSpecial xlValues
End With

You may need to change Activesheet to

sheets("NAME OF SHEET")

Upvotes: 3

user4039065
user4039065

Reputation:

Value transfer is faster than a copy and paste.

dim lr as long
with worksheets("sheet1")
    lr = .cells(.rows.count, "AG").end(xlup).row
    .range(.cells(5, "AE"), .cells(lr, "AE")) = .range(.cells(5, "AG"), .cells(lr, "AG")).value
end with

Upvotes: 2

Related Questions