Scenerio:
Col M has data in Rows 6-10.
I would like to take a value from Col. N, row 5 and paste it into rows 6-10 of the same column (N).
Using autofill increments the value from N5 through 6-10, which I don't want and my existing recorded macro pastes the value into more empty rows than I even want to bother counting. 🙂
Is there a line or two of code that would say - paste the copied value into Col. N for as many rows are used in Col. M?
Unfortunately this file is massive, even when I crop it down to almost nothing so the best I can do is provide a screen shot.
Hopefully it'll help with making sense of what I'm trying to do.
I would appreciate any help,
Thanks, Brenda
Hi Brenda,
You have no attachment, not even an image. Press the Start Upload button after you Add a file, only then the file will be uploaded.
I think you might have unnecessary formattings, this is one of the reasons why the size of a file is unnusually large. You can also paste a sample of your data into a new clean workbook, so we can see what you need.
Cheers,
Catalin
Hi, Catalin
Thanks for the reply! It seems like I have been here forever but I did manage to get an alternative -
Range("N5:N" & LastCell).Value = 1
As for the size of my file- thanks for the suggestion. I thought I had indeed previously tried as you suggested, unsuccessfully, but I did it again and BOOM. From 20,695kb down to 2546.
As always, MOTH Rocks!
Stay safe and well.
And thanks again, Brenda
I see you found an alternative; however, I just wanted to post some code that I use all the time to fill a column based on contents of another column.
Range("N5").Select
Range("N5").Copy Destination:=Range("N5:N" & Cells(Rows.Count, "M").End(xlUp).Row)
Going further, I mostly use the code above to copy a formula down such as:
Range("C2").Value = "=IF(ISNUMBER(A2),TEXT(A2,""000000""),A2)" 'formula I want to apply to column A to zero fill if it is a number
Range("C2").Select
Range("C2").Copy Destination:=Range("C2:C" & Cells(Rows.Count, "A").End(xlUp).Row) 'copy cell C2 down for as many rows are populated in column A
Range("C:C").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'copy and paste the formulas as values
Range("C:C").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste 'move the new values from column C to column A
Range("A1").Value = "Level 1" 'rename the header to the original header
And tbeards solution will be more "elegant" if you avoid some "Select", that normally are not necessary. Example:
Range("N5").Select
Range("C2").Select
In this case, both ranges refer to Activesheet.
HTH.
K-Li-Ch
