• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Paste Special Values from Specific Cells to Last row where the data is present

S

Sg2209

New Member
Hi Friends ,

i am trying to paste my formulas in Special Value till the last row where data is present , i have tried and unable to get the proper solution
i have tried the below code however it is selecting the column B:E even i have put ilastrow still selecting the entire columns please help on the below code
Code: 
Columns("A:A").Select
Range("B:E").Copy
Range("B:E").PasteSpecial xlPasteValues
Range("B:E" & lastrow).PasteSpecial xlPasteValues
please review where i am missing out
 
Marc L said:
Hi !

At least explain where are the data, what to do ? As we can't guess …
​
[the data is in the Column A , there is a Full Name with Middle initials, i have split them in Column B to Column D and again in Column E i did Concatenate the LastName,FirstName , so there is all the formulas from B2 to E100 however sometimes data goes to Row 250 , sometimes 500 so Row is not fixed and that it why i need to copy the data from B2 to lastrow and then paste it in special values as these all are formulas , please assist me
Click to expand...
 

Do not use Reply button when it's just the last post, thanks !

Where LastRow is initialized in your code ?
No empty rows within data ?
​
 
Range("B:E" & lastrow).PasteSpecial xlPasteValues

i did use it here , i was not sure where i should i use it as i am still learning the VBA , seen seeral videos and gone through the posts .
 
As I can't guess 'cause of your tiny piece of code,
check first the value of your lastrow variable !

The last codeline in your initial post seems useless (like first one !)
as the source range must be equal in size to the destination range
like previous codeline …

Activate Macro recorder and proceed manually :
you will get you own free base code. (I wanna see it)
​
 
To add to Marc. Syntax is off on use of last row.
Code: 
Range("B:E" & lastrow).PasteSpecial xlPasteValues

It will give error as "B:E#" can't be interpreted as valid address.

When pasting to range. You only need top left (starting) cell of your paste range.

Ex:
Code: 
Range("B" & lastrow).PasteSpecial xlPasteValues

'Or alternately
Cell(lastrow, "B").PasteSpecial xlPasteValues
 
Sg2209, As Mark L and Chihiro already said/asked, where and how do you initiate and define the variable lastrow? As integer, double?
We do not see your full code, making it less easy to really help. Can you upload a workbook with sample data (few lines) and full code?
 
Thank you So much , Appreciate you for all the efforts you made and Very well explained , i got your codes and you are wonderful :)
 
You are welcome, and I'm glad you could read my comments. I was rather in a hurry, and made some mess to be honest. My code will never be at the level guys as Mark L, Vletm, Chihiro, ... would make it, though. Think I would give them all a heart attack... :p
And we don't want that!
 
You must log in or register to reply here.
Back
Top