VBA Code to Paste Data based on end row of coumn b

[fishandtril]

Hello

I need some help with my macro to paste data.
I am copying a value from C2 into Column A but only want to paste values to cells which contain data in column B
I normally use Shift Ctrl arrow down to find last cell but as this is column A it returns the very last cell in Excel (A1048575)

What do I need to do to only paste as far as the last cell in column B

Range("C2").Select
    Selection.Copy
    Range("A17").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A17:A1048575").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[RobP]

Hi,

would something like this fit your needs (if I understood your question well) ?

Essentially it finds the last row of data in ColB, then checks each row (from 2 to the last row) for some data in "C". If data is found, it puts value of cell "C2" into the relevant Row in ColA.

Sub copy_data()

last_row = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row  'check for last row used in Col 2 ("B")

For x = 2 To last_row

    If ActiveSheet.Range("B" & x).Value <> "" Then
   
        Range("A" & x) = Range("C2")
   
    End If

Next x

End Sub

Book1
	A	B	C	D
1
2			mydata
3	mydata	data
4	mydata	data
5
6	mydata	data
7
8	mydata	data
Sheet1

 

[fishandtril]

Thank you for your assistance.

Is there away to Ignore data in rows 1-16?

My headings are row 16 and I only want to paste from A17 to last row.

 

[RobP]

yes, of course - in this case you would change the count to start from 17 instead of (2). That number is effectively the row number you'll start from.

Sub copy_data()

last_row = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row  'check for last row used in Col 2 ("B")

For x = 17 To last_row

    If ActiveSheet.Range("B" & x).Value <> "" Then
   
        Range("A" & x) = Range("C2")
   
    End If

Next x

End Sub