Cut-paste values to the first empty cell of the same row in Excel using VBA

Ruslan

New Member
Joined
Feb 18, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi community!
I've been following the site for many years and got a lot of amazing VBA solutions here.
Now I have finally registered to seek for your kind help.

So I have an excel table with several hundreds rows. Users of the file enter values in the column date; not all cells of the column but usually 20-30 at a time, several times per year. This column is followed by columns date1, date2, date3 and so on and so forth. I was looking for a macro which can take each value from date (once entered by a user) and place it in the first empty cell of the same row.
In the simplified table on the picture, from the column date 15-Jan should go to column date3, 6-Feb to date4 (the table I believe will be automatically extended when the value is pasted next to its last column), 7-Feb to date2.

All the pieces of code which I found on web are focusing on columns rather than on rows, so I cannot come up with a workable solution alone even after hours spent for searching and testing.
Thanks for your help in advance with this simple but complex question
 

Attachments

  • simplified table.JPG
    simplified table.JPG
    20.3 KB · Views: 8

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
My definition of lc and your definition of it return fundamentally the same value.
Are you sure the code in #7 behaved different from your modified version in #9?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Ruslan

New Member
Joined
Feb 18, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Are you sure the code in #7 behaved different from your modified version in #9?
Absolutely. The result of your code:

Book1 (version 1).xlsm
BCDEF
1datedate1date2date3date4
221-Feb
321-Feb
403-Feb03-Feb
501-Mar21-Feb21-Feb01-Mar
603-Mar21-Feb03-Mar
721-Feb
821-Feb21-Feb
921-Feb
1002-Jan02-Jan
Sheet1


Your code modified by me (though I can't explain it, as it is driven by guesses rather than any kind of knowledge :D):

Book1 (version 1).xlsm
BCDE
1datedate1date2date3
221-Feb
321-Feb
403-Feb03-Feb
501-Mar21-Feb21-Feb01-Mar
603-Mar21-Feb03-Mar
721-Feb
821-Feb21-Feb
921-Feb
1002-Jan02-Jan
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,129,942
Messages
5,639,110
Members
417,073
Latest member
CBG_Christian

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