help with .Cells()

fayez_MrExcel

Active Member
Joined
Oct 29, 2005
Messages
435
Office Version
  1. 365
Platform
  1. Windows
Is there any way to write the syntax below in one line only? Thanks

.Cells(sRow, "A").Copy
Cells(dRow, "E").PasteSpecial xlValues
.Cells(sRow, "B").Copy
Cells(dRow, "F").PasteSpecial xlValues
.Cells(sRow, "Y").Copy
Cells(dRow, "G").PasteSpecial xlValues
.Cells(sRow, "AB").Copy
Cells(dRow, "H").PasteSpecial xlValues
Cells(dRow, "D").Value = serial_number
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Give this a try:
Code:
.Cells(sRow, "A").Copy Destination:= Cells(dRow, "E") 
.Cells(sRow, "B").Copy Destination:= Cells(dRow, "F")
.Cells(sRow, "Y").Copy Destination:= Cells(dRow, "G")
.Cells(sRow, "AB").Copy Destination:= Cells(dRow, "H") 
Cells(dRow, "D").Value = serial_number
 
Upvote 0

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,961
Office Version
  1. 365
Platform
  1. Windows
I don't know about 1 line, but I got it in 3:

Code:
Union(.Cells(srow, "A"), .Cells(srow, "B"), .Cells(srow, "Y"), .Cells(srow, "AB")).Copy
Range(Cells(drow, "E"), Cells(drow, "H")).PasteSpecial xlPasteValues
Cells(drow, "D") = serial_number
 
Upvote 0

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Ok, cheating a little bit but here’s it in 1!
Code:
Union(.Cells(srow, "A"), .Cells(srow, "B"), .Cells(srow, "Y"), .Cells(srow, "AB")).Copy Destination:=Range(Cells(drow, "E"), Cells(drow, "H")) : Cells(drow, "D") = serial_number
 
Upvote 0

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,961
Office Version
  1. 365
Platform
  1. Windows
Ok, cheating a little

I was really trying not to go there :confused:

Although I gotta say that is interesting, I wouldn't have expected that to paste values. As long as the source range is contiguous, it pastes formulas but when the source range is uncontiguous it pastes values.
 
Upvote 0

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Although I gotta say that is interesting, I wouldn't have expected that to paste values

Me neither, but I discovered this a few weeks ago. It's quite a handy shortcut against PasteSpecial xlPasteValues. I guess you could also use Range(Range1).Value = Range(Range2).Value.
 
Upvote 0

Forum statistics

Threads
1,190,700
Messages
5,982,371
Members
439,776
Latest member
mathewduffy

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