Copy certain range when a cell is not blank
Hi, all
I have this data where it shows the parameters with unit as well as the monthly data and average. My issue is I would like to copy paste column "parameters", "unit" and "average", only if "average" has some values. The copied data will be pasted in the same sheet. May I know how to start? I have tried to search for a solution but it's still a bit blurry to me.
Hereby I attached an Excel file that almost matches to my actual file. Your help will be really appreciated. Thanks in advance.
Give this a go...
Please Login or Register to view this content.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
Hi, Sintek
Thank you so much for this! I'm currently altering this code to suit my actual file hence, can I know what is meant by SpecialCells (12)? What is meant by 12?
ell_
Your sheet layout is just made for AdvancedFilter.
P.S.Please Login or Register to view this content.
You need to delete extra space at the end in P1 "Avg " to match with T3 "Avg".
Last edited by jindon; 01-10-2018 at 04:35 AM.
Hi, Jindon
I got an error - Application-defined or object-defined error, may I know why?
P/s: I have deleted the extra space
Named range plays funny sometimes.
Try this one then
Please Login or Register to view this content.
visible cells after filterwhat is meant by SpecialCells (12)
Hi, Sintek
Currently trying your code but it doesn't work as what I expected when I played with it. Here's my code:
The blank data in column AI are still copied to AL33. May I know why?Please Login or Register to view this content.
Capture.PNG
To Jindon, thank you so much for this! I might be able to use it to my other sheets.
Last edited by ell_; 01-10-2018 at 09:01 PM.
The 16 above is for column P, You are now using Column AI which is Col # 35Please Login or Register to view this content.
Upload a sample of your new setup...
Last edited by sintek; 01-11-2018 at 01:40 AM.
Hi, Sintek
I have changed 16 to 35 but the same thing happens. Is it probably due to the reason that my data starts at row 9?
Upload a sample...Can't say until I see...
Sintek,
Here is the edited Excel file which is similar to the actual file. The top rows (1-8) have some other data which will be ignored in this case. Thank you so much!
P/s: I'm also hiding the columns in between because I only want to see column B, C and AI in the actual file
Quite important to know which columns this data occupies...Otherwise this snippet of code won't workThe top rows (1-8) have some other data
Edit....Please Login or Register to view this content.
Not to worry...This workaround...
Please Login or Register to view this content.
Last edited by sintek; 01-11-2018 at 02:09 AM.
Hi Sintek,
Here's the updated Excel. In actual file, the parameters will increase or decrease, number of rows occupied will change from time to time. But the summarized table in AL33 will not change its position. Hope that clarifies. Thanks!
Do you now see how important actual worksheet setup is in order for us to assist...All 3 your uploads are different and hence the code has to be adapted to suite...
Please Login or Register to view this content.
Last edited by sintek; 01-11-2018 at 02:32 AM.
Thank you, Sintek! And my apology for the trouble.
May I know if this code be capable of filtering up to 50++ rows? Just in case if the data increases in the future.
Yep, filtering is always better than looping...See change in code Post #15 - red part - to speed up.
Thank you so much, Sintek! And sorry again for the trouble :/
Works like a charm.
Another curiosity is, what if I want to apply this code to do the same thing, but pasting it to different sheet? And in this case, I want to copy all the columns from B to AI?
I know I should change:
Please Login or Register to view this content.Please Login or Register to view this content.Right?Please Login or Register to view this content.
Copy code remains the same...Please Login or Register to view this content.
Thank you so much, Sintek!
Definitely a life saver.
Glad I could help. Thanks for rep +
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks