Hey i use this part of macro to move cells to left but when it runs my formula its ruinned. Macro wont run if sheet is protected.
Formula
Please Login or Register to view this content.
Original:
Formula:
Please Login or Register to view this content.
After macro:
Formula:Please Login or Register to view this content.
Last edited by PumSese; 05-27-2019 at 05:26 AM.
Administrative Note:
We would very much like to help you with your query, however you need to include code tags around your code.
Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.
Forum Rules (updated September 2018): please read them here.
How to use the Power Query code you've been given: help here. More about the Power suite here.
Hi,
A remark, avoid using select and activate,it's rarely necessary and it slows down your code.
This
does the same asPlease Login or Register to view this content.
To run macro on a protected sheet,example with your code.Please Login or Register to view this content.
Change pw with your real pasword.
For the formula problem.Please Login or Register to view this content.
What happens when you delete the $ in the formula?
Like this, but for the complete formula.
M5:M55 instead of M$5:M$55
Click the * Add Reputation below to say thanks.
Thank you for advice. I did it at first without $. This is example picture how it looks after macro.
Capture.PNG
Why you want to delete blank cell on M5:Z55? When you delete a range which is reference of a formula, the formula will be broken. That is normal.
VBA does not ruin the formula.
this is standard thing that Excel does when you delete lines in the range that is inside a formula. This is to avoid circular references or the formula starting to include data outside the originally assigned area causing wrong results.
The $ makes sure the range does not change size when you drag or copy the formula itself. It does not guarantee the range stays the same if you delete rows within the specified range.
If you do not want have blank rows but keep the formula it would be better to do hide the blank rows then to delete them.
If you post an example excel file (and not screenshots) and explain a bit more what you want to achieve then I am sure we will be able to help you come up with a working solution for your case.
Fixed it by using indirect in formula. Thank you all for help.
Last edited by PumSese; 05-29-2019 at 05:01 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks