Next available open cell in column

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,210
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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
54,315
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
4,210
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
54,315
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,989
Messages
5,834,737
Members
430,315
Latest member
bobh63

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