Brilliant! I've been (casually) coding VBA in Excel for >20 years and I didn't know some of these! Doh! Please keep doing "Another 5" videos until you run out of ideas. :) Thank you!
Please advise, I have a problem with my Excel sheets it has equations and simple codes to navigate between the sheets . But I have to work on 4 or may be 6 sheets at the same time. But Excel tends to crash every time and shows the message"Excel has stopped working " as soon as I got the enable macro button. Thank you
I have watched many, MANY! TH-cam Videos in order to improve and learn VBA. You are by far the BEST. Explaining the Fundamentals is what teaching is all about, not just "tricks". I am definitely signing Up for your Course. Excel not an easy topic to learn and not every-one has the ability to teach. Thanks for your VIDS. HUGE THUMBS UP.
Many thanks. Especially the array part. I shed a tear. Happiness? The fact I wasted so much time in the past? It is just the powerfull simplicity at the end.
Paul, Taking a step back to see the fundamentals is an interest tour. I wish I had taken that step almost two decades ago. VERY well done. Thanks. Always a pleasure to "see" you.
Many thanks for such clear, well-organized, and focused content. I'm the "Excel guy" in my workplace; there are a few TH-cam experts I rely on for effective tutoring to increase my knowledge. You're my TH-cam subscription for VBA.
@@charlielorenzo3644 Might I suggest playing around with the macro recorder. It will write the shittiest code possible but I found it nice to get a rough idea of what regular tasks in excel look like when you automate them. Plus it can be kinda fun to use too!
Paul, I rarely comment on any of the youtube videos and just move on most of the time. However, I just wished to make an exception here. I just wish to say that by far you are one of the most brilliant tutors on the internet (that I have come around) in the excel vba arena. Please keep up the good work. It is a pleasure to watch your informative videos. Thanks, Ravi.
Wow, I have been using Excel professionally for 20 plus years and, damn, just learned 5 new things. How I managed without knowing this all these years astounds me. Thank you, thank you, thank you.
I have been learning Excel VBA for several months now and what a mess I coded. I learned so much more in 30 minutes of your videos, that I will rewrite my code for a third time to be much cleaner and more efficient. Thanks
Hi Paul - I am new to this channel and wow, I am BLOWN away! I have written a zillion macros for work, and I completely confess I am amateur in the way I code. I do things very simply, mostly because I need to prove to myself that my loop or function or calculation a) captures and processes the records my logic says it should capture, and b) subsequent manipulation of that data is accurate. So, you can bet my code is bloated and often unnecessary. But it works. Once I am confident everything is working, i would LOVE to speed the code up, minimize where possible, and use all of the built in tools Excel VBA already provides. But I admit that I don't know them very well. Your channel - for me at least - provides the best, simplest, most complete explanations I think I have ever seen. I learned more about how to populate an array in one of your videos then I learned over the past 6+ years of tinkering with this stuff. THANK YOU! I hope you are able to keep it up. You have a fan in California!
I tend to use the sheet index but I am going to incorporate codename more now. You eliminated everything I hated with the array code... can't believe I have not seen that before...many thanks.
Very nice. I was literally trying to solve the "user changes sheet name" problem with out locking down the entire workbook and #1 fits the bill perfectly... Not to mention the rest of these tips are great too!
Great video Paul. I teach a VBA course at a Canadian university and we cover a lot of those techniques. Nice to see someone else setting everyone straight!
Thanks been writing Excel macros and VBA since the 90s did not know the CurrentRegion property. You do have to burn a column to use it, not a big issue as you can just change the background colour of the first row of that border column. Can also get last row or column much nicer than other methods and also grab the data as an array Dim MyRange1 As Range: Set MyRange1 = Sheet6.Range("A1").CurrentRegion ' Get Last row and column Dim LastRow1 As Long: LastRow1 = MyRange1.Rows.count 'Get Array From Range Dim MyRows1 As Variant: MyRows1 = MyRange.Rows ' also works with MyColumns = MyRange.Columns
I've been using (and learning) vba for many years and only recently learned about Debug Compile from this video, I used it (along with Option Explicit) on my Personal.xlsb workbook and it took me days to clean up the code! Great tutorials
Just started teaching myself Excel VBA, for very specific reasons, and very much appreciate your sharing you experience here for novices like me to benefit from! Definitely Subscribing to your channel and will most probably be buying guides from your website in the very near future once I master your basic info!
Great stuff. One thing I wish I'd known earlier is that when making an array from a range, it will always be a 1-based array, not 0-based, even if you use Option Base 0 for the module. Thanks, Chris! I've learned a lot from you.
Your videos are so awesome, so well organized. Everyone can tell you put a lot of hard effort and making it so easy to follow and learn. You're amazing and raising the bar for everyone!
Hi Paul.. thanks so much. These tips are GOLD!! Thanks to videos like this, your excellent web site and blog, and your paid course options, becoming good at VBA is available to anyone willing to put in the time. Thanks for sharing your knowledge and skills.. much appreciated and valued. Thumbs up!!
Hi everyone, I hope you will enjoy these useful things that I wish I knew when I started using Excel VBA. Please add any comment or queries below the video. Thanks all.
Omg that code name is great. I’ve been modifying my code for years because I link to another excel spreadsheet and the owner of that is always changing the name of the tabs and thus breaking my code. I’ll try it out tomorrow. Thanks
GREAT x 3 !!! I really enjoined this video, specially the SPLIT trick. Back in the 80's I worked writing code for a company that did a lot of mailing. We would get a "dirty" list of names, and had to "clean it up" before we could use it for mailmerge with WORD. I don't know if SPLIT existed then but if it did I wish I've know about it.
I suspect that VBA has a lot of built-in functions (like SPLIT, for example) that few VBA coders know about. If more people used VBA, perhaps that kind of knowledge would be more widespread than it is. But leave it to Sir Paul to dig it out, use it and explain it to us. Many thanks to the master!
Very nice videos, and great shortcuts. You not only explain the theory of the functions but also explain solutions to real life situations, and while doing you always have in mind the code speed, as using macros is about speeding processes up in the first place. All is great, but it will be even better if the videos are more organised. Thank you very much for the great content and especially the tips that really matter, game changers when dealing with troublesome scenarios and the pro insights that enable us perspective when approaching problems. Just what people need to get to the next level. Thank you very much again!
He is really a genius... His codes are short and fast!!
5 ปีที่แล้ว +3
Thanks a lot. Your website is one of the best out there for excel vba. I just found that TH-cam channel and immediately subscribed to it. Very useful videos.
Really glad to see some new VBA videos with such great instruction. I use VBA almost daily and find these tips really useful. Only feedback I have at the moment is that I wouldn't mind if the sounds of the mouse clicks were a lot quieter -- it's a little distracting. Please keep these coming!
Tip 1 I found a few years ago: makes things sooo much easier. I would add a related tip and that is to use Defined Names for cells: if you need to move the cells referred to in your code around on your sheet, the defined name goes with them and you don't have to change your code. Although you probably knew that already! :)
This is great. Thanks. I come from an Access background and am moving over to Excel. Current region is great for use with arrays. I use Ctrl + A to select it in my version of Excel.
All your video lessons not only are lifesavers but inspiring too. Among many unknows simply not knowing 𝐂𝐮𝐫𝐫𝐞𝐧𝐭𝐑𝐞𝐠𝐢𝐨𝐧 made my life unnecessarily difficult. Utilizing that along 𝐎𝐟𝐟𝐬𝐞𝐭 and 𝐑𝐞𝐬𝐢𝐳𝐞 wrote the function below in case someone wants to eliminate 1 or more rows at top/bottom or 1 or more columns at left/right. Any suggestion from you on this will be helpful for me/us, I guess. Private Sub TryGetRegion() Dim rg As Range ' Row 1 has the header and the bottom row is for calculations, data starts from A2 Set rg = GetRegion(shProducts, "A2", 1, 1) End Sub ' -- Returns a range based on current region of a requested cell and ' -- optionally clipping some surrounding rows/columns Public Function GetRegion(sh As Worksheet, PointerCell As String, _ Optional SkipTopRows As Long = 0, _ Optional SkipBotRows As Long = 0, _ Optional SkipLftCols As Long = 0, _ Optional SkipRgtCols As Long = 0) As Range
Dim rg As Range: Set rg = sh.Range(PointerCell).CurrentRegion
If SkipTopRows 0 Then Set rg = rg.Offset(SkipTopRows).Resize(rg.Rows.Count - SkipTopRows) If SkipBotRows 0 Then Set rg = rg.Resize(rg.Rows.Count - SkipBotRows) If SkipLftCols 0 Then Set rg = rg.Offset(ColumnOffset:=SkipLftCols).Resize(ColumnSize:=rg.Columns.Count - SkipLftCols) If SkipRgtCols 0 Then Set rg = rg.Resize(ColumnSize:=rg.Columns.Count - SkipRgtCols)
I do enjoy Paul's knowledge and coding style. Since I have seen him work, I am familiar with many of these tips as he uses them frequently. The best takeaway for me was learning of the InstrRev VBA function. Thanks!
I wish I had known the first hint! As for the code in 4:39 vs 5:29 it is worth pointing out that the code in the second solution runs much faster than in the first one. The speed difference is noticeable in big projects (large arrays and ranges).
Very informative. The only thing that makes me sad is that you've started doing these videos much later than I started learning VBA. Would have saved me tons of time. Keep up the good work!
Great videos Paul. Have helped me a lot. Thank you. If the user makes a copy of the worksheet in the same workbook, so there are now two worksheets in the workbook, the second worksheet is given a new codename. Is there a way to make the VBA work with this second sheet?
For Point #4 you should mention that the the default delimiter for the Split() function is a single space character. So, if a space character is the delimiter you kneed, as in your code example, specifying the space character is redundant. So, this is equivalent to the code line in the video: arr = Split(rg.Cells(i, 1))
@@Excelmacromastery Providing default arguments is explicit. Whether that is clearer or not is an opinion. My opinion is that if you are learning a language, you take on the responsibility to understand the nuances of the language. Seeing Split() work without a delimiter specified, it is extremely obvious what is happening.
In stead of typing the full address of the destination cells you could use the size of the current region to determine the size. It is more flexible and you don't need to think column H +4 is column K. For example: Sub Arr() Dim rCR As Range Set rCR = Range("A1").CurrentRegion Range("H1").Resize(rCR.Rows.Count, rCR.Columns.Count).Value = rCR.Value End Sub If for example the number of rows increases you do not need to alter the above code.
Ur excel videos are awesome very clear on teaching... Sir how to clear macro or refresh macro to run again, for example I have copied data from one workbook and vlookup from that copied range with two vlookup codes am using for two values, kindly advise how to refresh and run macro for updated datas
Hi Sir keep up the good work that you are doing, helping others especially non-programmers, I've been doing a little bit of coding and its beginning to interest . I have a problem with one of my userforms especially interms of coding, would you mine if I share my form with you for some expert help from you Thanks!!
Thankyou Sir thats fine I was just asking because my day to day work is centered around data and management of information thats why I was just asking because I'm not a programmer -otherwise, thankyou I appreciate your response
Thank you for putting all this information out there! One quick question for me though is I have an issue with their being blank rows in the range. The code I normally use to find the number of rows is Range(Cells(1,1),Cells(rows.count,1).end(XlUp)).Count, and the same idea for columns if I don't know how many columns the range has. Is there a was to just use the '.CurrentRegion' that would ensure you got all of the range even if there are blank rows within the range?
I still remember my instructor telling me that proper coding is being verbose so you can always know what you are writing code for. Changing the code name to a descriptive name is so much more useful that ThisWorkbook.Sheets("Sheet1")... It was drilled in to not be lazy and type out the implicit name. Well instructor (who will remain nameless), I agree with Paul that using a name for a sheet that end users do not have access to is way more efficient and can also be a self documenting feature as well if your sheets are code named efficiently.
HaHa! I am at a basic level, so this is so vital for me, as I am already guilty of doing the long winded methods. I am begining to realize as human, our logical construct is sequential, whereas the code logic handles in blocks. Would that be an accurate assessment?
Brilliant! I've been (casually) coding VBA in Excel for >20 years and I didn't know some of these! Doh! Please keep doing "Another 5" videos until you run out of ideas. :) Thank you!
That's really good to hear Chris. I think I'll be able to come up with another few useful tricks.
The same is valid for me. Just one word: Whow!
Please advise, I have a problem with my Excel sheets it has equations and simple codes to navigate between the sheets . But I have to work on 4 or may be 6 sheets at the same time. But Excel tends to crash every time and shows the message"Excel has stopped working " as soon as I got the enable macro button.
Thank you
Save here!
for real ? lol, took me 3 months on self learning to know that
This guy is the best out there by far. For anyone trying to learn VBA for Excel, start here.
I have watched many, MANY! TH-cam Videos in order to improve and learn VBA. You are by far the BEST. Explaining the Fundamentals is what teaching is all about, not just "tricks". I am definitely signing Up for your Course. Excel not an easy topic to learn and not every-one has the ability to teach. Thanks for your VIDS. HUGE THUMBS UP.
Many thanks. Especially the array part. I shed a tear. Happiness? The fact I wasted so much time in the past? It is just the powerfull simplicity at the end.
I have been an intermediate VBE coder for years (do not want to be advanced) - but your channel is great.
The array portion was amazing! I didn't know it could use it like that. Definitely a game changer for me.
Paul, Taking a step back to see the fundamentals is an interest tour. I wish I had taken that step almost two decades ago. VERY well done. Thanks. Always a pleasure to "see" you.
Thanks Warren. Glad you like it.
Great video. Thanks. I'm on a mediocre level but codename was one of the 1st things I have learned from you. Now working on arrays.
Thanks for the feedback Jan.
I love your calm pleasant speaking style. Bonus is succinct, well organized steps of instruction. I give an A+ to this Instructor.
Thanks😀
At least 15 years of struggling with Arrays, Range limits and parsing text. I wish I’d known these too. You have one new sub. Thank you.
Great to have you on board Simon.
Many thanks for such clear, well-organized, and focused content. I'm the "Excel guy" in my workplace; there are a few TH-cam experts I rely on for effective tutoring to increase my knowledge. You're my TH-cam subscription for VBA.
That's great to hear Sam. I'll be adding lots more content.
Using VBA for 2 decades. I did not know any these. Thanks a lot mate. More please... Cheers.
Many thanks, with arthritic fingers the less typing I do the easier it is for me . This site is a God send .Thanks again.
Literally started vba this month and found this priceless 🙂. As are your others, especially those relating to error handling.
I still cannot figure it out where to start
@@charlielorenzo3644 Might I suggest playing around with the macro recorder. It will write the shittiest code possible but I found it nice to get a rough idea of what regular tasks in excel look like when you automate them. Plus it can be kinda fun to use too!
Paul,
I rarely comment on any of the youtube videos and just move on most of the time.
However, I just wished to make an exception here.
I just wish to say that by far
you are one of the most brilliant tutors on the internet (that I have come around) in the excel vba arena.
Please keep up the good work. It is a pleasure to watch your informative videos.
Thanks,
Ravi.
Thanks so much Ravi. That is very high praise indeed.
I knew all of them, but the way you use the watch window is something I've never seen and now will use more frequently! Thanks!
Glad you like it Michael.
Wow, I have been using Excel professionally for 20 plus years and, damn, just learned 5 new things.
How I managed without knowing this all these years astounds me.
Thank you, thank you, thank you.
Just knowing that people can't break the macro by renaming the sheet is a dream! Thank you so much!
You're welcome:-)
'Codename' cleans up the code. Excellent! Subscribed.
Glad you like it.
I have been learning Excel VBA for several months now and what a mess I coded. I learned so much more in 30 minutes of your videos, that I will rewrite my code for a third time to be much cleaner and more efficient. Thanks
You're welcome Michael.
Hi Paul - I am new to this channel and wow, I am BLOWN away! I have written a zillion macros for work, and I completely confess I am amateur in the way I code. I do things very simply, mostly because I need to prove to myself that my loop or function or calculation a) captures and processes the records my logic says it should capture, and b) subsequent manipulation of that data is accurate. So, you can bet my code is bloated and often unnecessary. But it works. Once I am confident everything is working, i would LOVE to speed the code up, minimize where possible, and use all of the built in tools Excel VBA already provides. But I admit that I don't know them very well. Your channel - for me at least - provides the best, simplest, most complete explanations I think I have ever seen. I learned more about how to populate an array in one of your videos then I learned over the past 6+ years of tinkering with this stuff. THANK YOU! I hope you are able to keep it up. You have a fan in California!
Thanks very much for the feedback Terry. That is high praise indeed. I appreciate it.
i'm late but still wanted to say thank you for these tips. each and everyone of them is pure gem
Paul, I know what you are doing takes a lot of effort to produce. Thanks so much! Your tips are invaluable.
Glad you like them!
Hi EMM, you cannot even imagine what you have done for me and my department... Thank you is not enough!
Thanks Ivan. You're very welcome.
I tend to use the sheet index but I am going to incorporate codename more now. You eliminated everything I hated with the array code... can't believe I have not seen that before...many thanks.
Glad to help
Very nice. I was literally trying to solve the "user changes sheet name" problem with out locking down the entire workbook and #1 fits the bill perfectly... Not to mention the rest of these tips are great too!
Great video Paul. I teach a VBA course at a Canadian university and we cover a lot of those techniques. Nice to see someone else setting everyone straight!
Glad you like them:-)
Thanks been writing Excel macros and VBA since the 90s did not know the CurrentRegion property.
You do have to burn a column to use it, not a big issue as you can just change the background colour of the first row of that border column.
Can also get last row or column much nicer than other methods and also grab the data as an array
Dim MyRange1 As Range: Set MyRange1 = Sheet6.Range("A1").CurrentRegion
' Get Last row and column
Dim LastRow1 As Long: LastRow1 = MyRange1.Rows.count
'Get Array From Range
Dim MyRows1 As Variant: MyRows1 = MyRange.Rows
' also works with MyColumns = MyRange.Columns
The use of a variant array for copying range to an array and split function is very helpful. Thank you!!
You're welcome Sharmin.
I've been using (and learning) vba for many years and only recently learned about Debug Compile from this video, I used it (along with Option Explicit) on my Personal.xlsb workbook and it took me days to clean up the code! Great tutorials
Just started teaching myself Excel VBA, for very specific reasons, and very much appreciate your sharing you experience here for novices like me to benefit from!
Definitely Subscribing to your channel and will most probably be buying guides from your website in the very near future once I master your basic info!
Thanks Watt. Glad you liked it.
Muchas gracias por video! Estoy justo aprendiendo VBA y aprendiendo que los arrays hacen más eficiente mi código.
What can I say, your videos are BRILLIANT! Please keep making more! I feel my weekends are so much more productive!!!
Thanks for your nice comment.
@@Excelmacromastery Thank YOU for sharing your knowledge!!
Great stuff. One thing I wish I'd known earlier is that when making an array from a range, it will always be a 1-based array, not 0-based, even if you use Option Base 0 for the module. Thanks, Chris! I've learned a lot from you.
Who's Chris?😄
@@Excelmacromastery Ha! Sorry, Paul. You can tell I'm new here.
Your videos are so awesome, so well organized. Everyone can tell you put a lot of hard effort and making it so easy to follow and learn. You're amazing and raising the bar for everyone!
Thanks very much David. That is high praise indeed.
Hi Paul.. thanks so much. These tips are GOLD!! Thanks to videos like this, your excellent web site and blog, and your paid course options, becoming good at VBA is available to anyone willing to put in the time. Thanks for sharing your knowledge and skills.. much appreciated and valued. Thumbs up!!
You're welcome Wayne. As always, thanks for the great feedback.
Worksheet code name just solved a problem I was thinking about. Thanks!
Hi everyone,
I hope you will enjoy these useful things that I wish I knew when I started using Excel VBA.
Please add any comment or queries below the video.
Thanks all.
Hi.. Which video editor, screen recorder do you use and audio recorder as well?
Thanks Paul, great stuff.
Assalamualaikum sir i am toufikul alam from dhaka bangladesh. Brilliant idea. i am fan of you vba tutorials. greet thanks
Omg that code name is great. I’ve been modifying my code for years because I link to another excel spreadsheet and the owner of that is always changing the name of the tabs and thus breaking my code. I’ll try it out tomorrow. Thanks
You're welcome.
GREAT x 3 !!! I really enjoined this video, specially the SPLIT trick. Back in the 80's I worked writing code for a company that did a lot of mailing. We would get a "dirty" list of names, and had to "clean it up" before we could use it for mailmerge with WORD. I don't know if SPLIT existed then but if it did I wish I've know about it.
Thanks. Yes, split is a great function - My next video will show examples of using split.
I suspect that VBA has a lot of built-in functions (like SPLIT, for example) that few VBA coders know about. If more people used VBA, perhaps that kind of knowledge would be more widespread than it is.
But leave it to Sir Paul to dig it out, use it and explain it to us. Many thanks to the master!
This is the one of important lessons to take from experienced experts
Thanks glad you like it.
1st and 3rd one are easy and brilliant. i never knew that. it would be very helpful. thxs
Thanks for sharing, I've learnt with you how to copy range to array easily, it helped me out a lot
Thanks all 5 were new information for me. Thanks for sharing your invaluable knowledge!!
Thank you for these brilliant information. Specially the codename for the sheet and the current region and the other tipps.
You're very welcome!
First-rate exposition. I will use these tips regularly. Thanks.
You're welcome
Very nice videos, and great shortcuts. You not only explain the theory of the functions but also explain solutions to real life situations, and while doing you always have in mind the code speed, as using macros is about speeding processes up in the first place. All is great, but it will be even better if the videos are more organised. Thank you very much for the great content and especially the tips that really matter, game changers when dealing with troublesome scenarios and the pro insights that enable us perspective when approaching problems. Just what people need to get to the next level. Thank you very much again!
Thanks for the feedback. Glad you like the videos.
He is really a genius... His codes are short and fast!!
Thanks a lot. Your website is one of the best out there for excel vba. I just found that TH-cam channel and immediately subscribed to it. Very useful videos.
Thanks for the feedback. Glad you like it.
Oh wow... Code Names just blew my mind right now.
Really glad to see some new VBA videos with such great instruction. I use VBA almost daily and find these tips really useful. Only feedback I have at the moment is that I wouldn't mind if the sounds of the mouse clicks were a lot quieter -- it's a little distracting. Please keep these coming!
Thanks Adam.
Very helpful to improve my coding. This is brilliant.
Great to hear!
Thank you for this straight forward tutorial.
your channel is awesome! your code is very clean and elegant!
Thanks Byron
Hi Paul, I agree - I wish that I knew them before starting out with VBA. Finding LastRow was generally trial and error.
Thanks for the feedback John.
I've been coding and this was very helpful in glad I stomped on your video thank you very much
You're welcome.
Tip 1 I found a few years ago: makes things sooo much easier. I would add a related tip and that is to use Defined Names for cells: if you need to move the cells referred to in your code around on your sheet, the defined name goes with them and you don't have to change your code. Although you probably knew that already! :)
Awesome advice! I was blown away by the simple Array code.
Thanks Jan. Most people are amazed when the see it first - myself included.
This is great. Thanks. I come from an Access background and am moving over to Excel. Current region is great for use with arrays. I use Ctrl + A to select it in my version of Excel.
Thanks Tom. Keep in mind that Ctrl + A(select all) is different than Ctrl + * (i.e. select current region)
Your best video is that about using both arrays and dictionaries
Thanks Rik
so much time lost finding ranges... glad i clicked this suggested video. ty man
All your video lessons not only are lifesavers but inspiring too. Among many unknows simply not knowing 𝐂𝐮𝐫𝐫𝐞𝐧𝐭𝐑𝐞𝐠𝐢𝐨𝐧 made my life unnecessarily difficult. Utilizing that along 𝐎𝐟𝐟𝐬𝐞𝐭 and 𝐑𝐞𝐬𝐢𝐳𝐞 wrote the function below in case someone wants to eliminate 1 or more rows at top/bottom or 1 or more columns at left/right. Any suggestion from you on this will be helpful for me/us, I guess.
Private Sub TryGetRegion()
Dim rg As Range
' Row 1 has the header and the bottom row is for calculations, data starts from A2
Set rg = GetRegion(shProducts, "A2", 1, 1)
End Sub
' -- Returns a range based on current region of a requested cell and
' -- optionally clipping some surrounding rows/columns
Public Function GetRegion(sh As Worksheet, PointerCell As String, _
Optional SkipTopRows As Long = 0, _
Optional SkipBotRows As Long = 0, _
Optional SkipLftCols As Long = 0, _
Optional SkipRgtCols As Long = 0) As Range
Dim rg As Range: Set rg = sh.Range(PointerCell).CurrentRegion
If SkipTopRows 0 Then Set rg = rg.Offset(SkipTopRows).Resize(rg.Rows.Count - SkipTopRows)
If SkipBotRows 0 Then Set rg = rg.Resize(rg.Rows.Count - SkipBotRows)
If SkipLftCols 0 Then Set rg = rg.Offset(ColumnOffset:=SkipLftCols).Resize(ColumnSize:=rg.Columns.Count - SkipLftCols)
If SkipRgtCols 0 Then Set rg = rg.Resize(ColumnSize:=rg.Columns.Count - SkipRgtCols)
Set GetRegion = rg
End Function
I do enjoy Paul's knowledge and coding style. Since I have seen him work, I am familiar with many of these tips as he uses them frequently.
The best takeaway for me was learning of the InstrRev VBA function. Thanks!
Thanks for the feedback Patrick. You can see all the VBA string functions here: excelmacromastery.com/vba-string-functions/
Omg that codename is very useful thanks. Of course everything in excellent keep on videos like this
The video is very useful for me.I learn a lot tickets,thanks.
I wish I had known the first hint! As for the code in 4:39 vs 5:29 it is worth pointing out that the code in the second solution runs much faster than in the first one. The speed difference is noticeable in big projects (large arrays and ranges).
Thanks for the videos - super helpful and very well presented.
Thanks for the feedback Michael.
That is very insightful and of a great help for a 'VBA novice' like me. Thanks a lot.
Thanks Vivek. Glad you like it.
Thank you for the video. Really helpful. I believe we could get the current.region by clicking in a cell and CTRL+a
Ctrl+A is works differently that current region. It means "select all" in most applications.
Thanks! very good tutorial. Short, clear and concise.
glad you like it.
Great job. Great teacher!
Great Video! Thanks for sharing your knowledge!
Hi, yes that would have been really great to know at the beginning already. Thanks a lot for sharing! 🙂
Glad it was helpful!
@@Excelmacromastery it was 👍🏻
Paul, I too wish I knew these beforehand.
Thanks Sandeep. Glad you like them.
Holy crap, just what I needed. Subscribed. I've learned something new in every video.
Thanks Timmy. Glad you like it.
Very informative. The only thing that makes me sad is that you've started doing these videos much later than I started learning VBA. Would have saved me tons of time. Keep up the good work!
Thanks Martynas. Glad you like them.
Great videos Paul. Have helped me a lot. Thank you. If the user makes a copy of the worksheet in the same workbook, so there are now two worksheets in the workbook, the second worksheet is given a new codename. Is there a way to make the VBA work with this second sheet?
For Point #4 you should mention that the the default delimiter for the Split() function is a single space character. So, if a space character is the delimiter you kneed, as in your code example, specifying the space character is redundant. So, this is equivalent to the code line in the video:
arr = Split(rg.Cells(i, 1))
It's better to explicitly show the parameter as it makes the code clearer.
@@Excelmacromastery Providing default arguments is explicit. Whether that is clearer or not is an opinion. My opinion is that if you are learning a language, you take on the responsibility to understand the nuances of the language. Seeing Split() work without a delimiter specified, it is extremely obvious what is happening.
In stead of typing the full address of the destination cells you could use the size of the current region to determine the size.
It is more flexible and you don't need to think column H +4 is column K.
For example:
Sub Arr()
Dim rCR As Range
Set rCR = Range("A1").CurrentRegion
Range("H1").Resize(rCR.Rows.Count, rCR.Columns.Count).Value = rCR.Value
End Sub
If for example the number of rows increases you do not need to alter the above code.
Thanks Ron. I was just using the full address for the example. Obviously in a real-world example it wouldn't be hard coded.
As always, very useful and clear. Thanks Paul.
You're welcome João.
Simple and brilliant.
Earned a sub! 'Split' is going to be very useful to me and I can already think of an application for that 'array' code, thanks!
Thank you very much. It's really helpful.
Thanks from Norway! I just klicked the subscribe button, and will be checking out a lot of your videos!
Thanks Jim.
Ur excel videos are awesome very clear on teaching... Sir how to clear macro or refresh macro to run again, for example I have copied data from one workbook and vlookup from that copied range with two vlookup codes am using for two values, kindly advise how to refresh and run macro for updated datas
I don't understand what you mean by "Clear Macro" or "refresh macro". Do you mean the data?
@@Excelmacromastery
Refresh Macro, to clear the data and run macro again for updated datas
Waiting for your reply sir
Still no reply maybe u also don't know the answer for my question 😂😂
Hi, you are a legend. Thank you so much!
Thanks for your comment Marcus.
Like the range to array. Thanks a handy one.
Thanks Mark.
Hi Sir keep up the good work that you are doing, helping others especially non-programmers, I've been doing a little bit of coding and its beginning to interest . I have a problem with one of my userforms especially interms of coding, would you mine if I share my form with you for some expert help from you Thanks!!
Hi Baliong. It's not possible for me to help with individual projects.
Thankyou Sir thats fine I was just asking because my day to day work is centered around data and management of information thats why I was just asking because I'm not a programmer -otherwise, thankyou I appreciate your response
all tips are very usefull,thanks!
Thank you for putting all this information out there! One quick question for me though is I have an issue with their being blank rows in the range. The code I normally use to find the number of rows is Range(Cells(1,1),Cells(rows.count,1).end(XlUp)).Count, and the same idea for columns if I don't know how many columns the range has. Is there a was to just use the '.CurrentRegion' that would ensure you got all of the range even if there are blank rows within the range?
CurrentRegion ends at blank rows or columns. You could have a look at UsedRange and see if this can help.
Great video. As usual. It saved me a lot of time. Thank you, Paul.
Good to here Maciej
I still remember my instructor telling me that proper coding is being verbose so you can always know what you are writing code for. Changing the code name to a descriptive name is so much more useful that ThisWorkbook.Sheets("Sheet1")... It was drilled in to not be lazy and type out the implicit name. Well instructor (who will remain nameless), I agree with Paul that using a name for a sheet that end users do not have access to is way more efficient and can also be a self documenting feature as well if your sheets are code named efficiently.
The code name was a great find. I often use ThisWorkbook.Worksheets in examples as I always get questions about where did I get shData from:-)
The first one was goooood 👍
Brilliant advice!
Very helpful tips! Thanks so much.
Nice, I knew 2 out of 3! Good video thx
HaHa! I am at a basic level, so this is so vital for me, as I am already guilty of doing the long winded methods.
I am begining to realize as human, our logical construct is sequential, whereas the code logic handles in blocks. Would that be an accurate assessment?
Very useful tips!
Awesome, great ideas. Thank you very much ☺