how to select column till last non empty cell

Question

I am creating vba macro to copy a column having set of values. but in between some rows are blank in that column.how to code it dynamically so that it will select till last non empty cell.

for example in cell A if A1 to A20 have values A21 is blank A22 to A35 have values A36,A37,A38 is blank A39 to A45 hav values

Now i need to dynamically select values from A1 to A45 and paste it in B. Also values in A Come through formula.I want to carry that formula and paste it as formula

how do i do it?

Answer 1

Please try the below code..

Sub test()
'change the sheet name as yours
Sheets("Sheet1").Activate
Range("A1", Cells(Rows.Count, 1).End(xlUp)).Copy
'Change the destination range as yours
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
End Sub

Answer 2

To work with all cells from A1 up to the last non-empty cell in column A:

Dim oAllDataInA As Range
With Worksheets("Sheet1")
    Set oAllDataInA =.Range("A1", .Range("A" & .Rows.Count).End(xlUp))
End With
'Now work with oAllDataInA. For instance. let formula in column B be same as in A:
oAllDataInA.Copy
oAllDataInA.Offset(,1).PasteSpecial xlFormulas