VBA help: copy select characters in string and paste until blank row

[snajam23]

Hello all,

Hoping someone would know the answer

I would like to copy and paste data within the same sheet like below.

Company: ABC123 in cell A1

I would like to copy the last 6 characters in this string meaning the characters starting from A.

Assume theres data in rows 2 through 4 in columns A B and C. Row 5 is blank.

I would like to paste the data as paste values in column D starting in 2 and stop when it reaches a blank row. Meaning the paste values should stop in row 4.

Hope my description is clear.

Thanks in advance.

 

[JoeMo]

Try:

Sub Last6()
Dim lR As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
With Range("D1").Resize(lR, 1)
    .Value = Right(Range("A1").Value, 6)
End With
End Sub

 

[snajam23]

Thank you @JoeMo

Looks like the code is copying the value as intended but its pasting the data all way down to the last row in the sheet. It is not stopping before the first blank row.

 

[JoeMo]

Thank you @JoeMo

Looks like the code is copying the value as intended but its pasting the data all way down to the last row in the sheet. It is not stopping before the first blank row.

Post your data using XL2BB so we can see the data layout. In the worst case I can think of, col A is empty, including A1, the "paste" would stop after cell D1. Did you modify the code in any way? Did you copy it from your browser and paste it to the VBE or did you re-type it?

 

[snajam23]

so i think i figured out why he paste is going down more than expected rows. According to my explanation i believe my data set looks as image 1 below. i thought the paste values would stop before the blank rows but if there is additional data after a set of blank rows then the paste continues.

Does it make more sense to define the range to paste the values instead of paste until it reaches a blank row?

Image 1

Asset Additions Report 8.18.xlsx
	A	B	C	D	E
1	Company: ABC123			ABC123
2	abc	def	ghi	ABC123
3	abc	def	ghi	ABC123
4	abc	def	ghi	ABC123
5
Sheet2

Image 2

Asset Additions Report 8.18.xlsx
	A	B	C	D	E
1	Company: ABC123			ABC123
2	abc	def	ghi	ABC123
3	abc	def	ghi	ABC123
4	abc	def	ghi	ABC123
5				ABC123
6				ABC123
7				ABC123
8				ABC123
9				ABC123
10	Company: DEF123			ABC123
11	abc	def	ghi	ABC123
12
Sheet2

 

[JoeMo]

You should include details like the possibility of full blank rows within the data.
See if this fixes the problem:

Sub Last6()
Dim lR As Long
lR = Range("A1").CurrentRegion.Rows.Count
With Range("D1").Resize(lR, 1)
    .Value = Right(Range("A1").Value, 6)
End With
End Sub