Log In
Register
Home
Active Member
June 16, 2015
Offline
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
See Answer
November 8, 2013
Offline
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
Answers Post
Active Member
June 16, 2015
Offline
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
New Member
June 27, 2016
Offline
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
New Member
July 9, 2016
Offline
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
1 Guest(s)
