Next available open cell in column

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a column of empty cells A2:A25.
How can I code a paste function that will paste a copied value (only) into the next open cell within that range.

Jenn
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
Range("A1").Copy Range("A2").End(xlDown).Offset(1, 0)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
If the cells are filled sequentially, you could try something like:

YourRange.Copy Destination:=Range("A" & Rows.Count).End(xlup).Offset(1)

If that is not it, try to provide a bit more detail and/or examples.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Range("A25").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks you gentlemen!
I haven't tried them yet, but Peter, suppose I wanted to do some error checking. I do not want to paste a value, or range of values, into the next available cell and exceed the 25 cell available "zone". Is there a way I can extract a value from your suggestion? ie a way to set value to where that formula is going to drop the value(s).

Jenn
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Thanks you gentlemen!
I haven't tried them yet, but Peter, suppose I wanted to do some error checking. I do not want to paste a value, or range of values, into the next available cell and exceed the 25 cell available "zone". Is there a way I can extract a value from your suggestion? ie a way to set value to where that formula is going to drop the value(s).

Jenn
If the Peter is me rather than Peter (VoG) then you could try something like this
Code:
Dim lNextRw As Long

lNextRw = Range("A" & Rows.Count).End(xlUp).Row + 1
If lNextRw < 26 Then
    YourRange.Copy Destination:=Range("A" & lNextRw)
Else
    MsgBox "Row 25 already used"
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top