CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
h.barry
h.barry

Reputation: 27

selecting 30 cells from the bottom active text to up excel vba

Hello i would like to copy the last 30 cells of my column B.

the below code is copying to right, but i want it to copy only the 30 last cells of the column b

Sub test()
Range(ActiveCell, ActiveCell.Offset(0, 30)).Copy
End Sub

kindly help. best regards

Upvotes: 0

Views: 918

Answers (4)

user3598756
user3598756

Reputation: 29421

you could try this:

With Cells(Rows.Count, "B").End(xlUp)
    Range(.Cells, .Offset(WorksheetFunction.Max(-.Row + 1, -29))).Copy
End With

and handle the case there are less than 30 last cells

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96773

One way:

Sub dural()
    Dim N As Long
    N = Cells(Rows.Count, "B").End(xlUp).Row
    Range("B" & N - 29 & ":B" & N).Copy
End Sub

Upvotes: 0

Brandon Barney
Brandon Barney

Reputation: 2392

In the future I strongly suggest trying to solve your problem by looking up the arguments for the functions you use. See fixed code below:

Sub test()
Range(ActiveCell, ActiveCell.Offset(-29, 0)).Copy
End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

Find the last row with data in Column B (including empty rows in the middle), and then copy the range (from 29 rows down till last row found) .

Note: try not to use ActiveCell if you can, instead use fully qualified Range.

Dim LastRow As Long

LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B" & LastRow - 29 & ":B" & LastRow).Copy

Upvotes: 1

Related Questions