From my understanding at approx 7:00 the best practice is to Erase CompanyName i.e. erase the array variable. Erase acts much like Unload Me.. although, it may not be a big deal, it's supposed to release memory. Thank you for the tutorial!
Hello sumit.. i am your no. 1 fan, can you make tutorial of the difference between Row, Rows, column, columns, Cell, Cells, worksheet, worksheets. etc .. thanks you so much
Your explanation is superb. No one can explain these topics so easily. I've recently come across this problem. Sir, would you kindly please make a video on this topic - "How to split Strings that contain no Delimiter using User Defined Function in Excel VBA?" For Example, - You have some client names on your Sheet1 from Range("A2") to Range("A16"): Raw Data: ======== Names First Names | Middle Names | Last Names ------------ -------------------- ------------------------ -------------------- AlexanderWilliamJamesBrown AmandaTaylor AriaNguyen AubreyFoster AveryAnnPhillips Beerus BenjaminThomasHarris CharlotteMitchell ChristopherJackson DanielJamesMiller DavidAndrewJohnWilliams Dende DylanJamesWard ElijahWatson EllaSophiaRoberts ========== Here, some clients have First Names, Middle Names and Last Names (For Example: Alexander William James Brown etc.); some have First Names and Last Names (For Example: Amanda Taylor etc.) and some have only First Names (For Example: Beerus etc.). Now, I want to delimitate each name based on the Capital Letter. Desired Output mentioned below: Desired Output: ============= Names First Names | Middle Names | Last Names ------------ -------------------- ------------------------ -------------------- AlexanderWilliamJamesBrown Alexander | William James | Brown AmandaTaylor Amanda | | Taylor AriaNguyen Aria | | Nguyen AubreyFoster Aubrey | | Foster AveryAnnPhillips Avery | Ann | Phillips Beerus Beerus BenjaminThomasHarris Benjamin | Thomas | Harris CharlotteMitchell Charlotte | | Mitchell ChristopherJackson Christopher | | Jackson DanielJamesMiller Daniel | James | Miller DavidAndrewJohnWilliams David | Andrew John | Williams Dende Dende DylanJamesWard Dylan | James | Ward ElijahWatson Elijah | | Watson EllaSophiaRoberts Ella | Sophia | Roberts Thank you, Sir, for uploading the video on my request. A few years ago, I commented this question to you. Y
Hi great videos and crystal clear Could please 🙏 solve the this error Dim j as variant, J= Array(cells(1, 10), cells(1, 20)). When watch this code in local window getting typescript error could please help me out of error.
Thanks Sumit. This is awesome! I have a question though. You used two separate loops. One for reading the data from the sheet and another for writing the data to a different area. Couldn't you have done both in the same loop?
In the offset u mention it is not clear where the negative sign comes from. If we use 1,0 then 2,0 etc so why do u write -1 ? May please clarify this part ? This is also repeated when using offset. dimension 1 and dimension 2, -1 was also used 🤔🤔
Can you please explain how to use array of array in VBA? this is my logic. i have multiples arrays that stores rows value like arrRow1 ,arrRow2 ,arrRow3 so now i want to store these arrays in a one array that is arrTable[arrRow1 ,arrRow2 ,arrRow3]. If I want to access any value then I'll use arrTable[0][1]:- 0 for arrRow1 and the one for the arrRow[1] how its possible in Vba.
here range ("a2") is activecell and In offset (x,y) here x = 1,y=1 means lower bound element start from 1 Then range ("a2"). offset (1,1). value will be store in range ("b3") as "Sinopec" so this will be wrong Now here we want to store value in range ("a2") then we need (-1) bcz here lower bound element start from 1 not from 0 If lower bound element from 0 then No need minus1(-1)
Sub arrays() Dim names() As String Dim dimensions1 As Long Dim j As Long dimensions1 = Range("a1", Range("a1").End(xlDown)).Cells.count ReDim names(1 To dimensions1) For j = 1 To dimension1 names(j) = Range("a1").Offset(j - 1, 0).Value Next j End Sub sir i am face very problem to solve this dyanamic array please help me
@@ishrafilansari2241 the only thing wrong in this code is that you have not used proper variable in the line For j = 1 To dimension1 it should be For j = 1 To dimensions1 you were missing an "s" just before the Dimensions.
You have greatest teaching skills...amazing and biggest thing is you giving it FREE of cost.
stay healthy,stay happy
yes…its really helpful
Amazingly beautiful language in which you explained the Array with perfect clarity... thanks!
Best video I've viewed explaining Excel VBA Arrays. Thanks for including the Excel file.
Awesome explanation of array. You made it simple to understand. Thanks a lot.
Very well explained and great examples. This man is a genius and a great teacher
Thank you so much for the kind words 🙏
Excellent Tutorial! Thank you.
This video is super useful, I appreciate it very much, keep doing these videos.
Thank you.
Glad the video helped :)
beautifully explained....I was in search of such specific video on arrays. Thanks..
A lot thankyou, Now have no doubt in array ✅
Very well explained, thank you very much.
Glad you found the video useful!
Thankyou sir for this detailed video of using arrays in vba.
This helped me greatly with my understanding of arrays. Thank you.
Thank you bro. Very clear and concise the way you explained. I had an issue which you just resolved! I have Subscribed
god bless you man
💕
Thank you Sumit for detailed information.;
Really very nice video
nicely done!
From my understanding at approx 7:00 the best practice is to Erase CompanyName i.e. erase the array variable. Erase acts much like Unload Me.. although, it may not be a big deal, it's supposed to release memory. Thank you for the tutorial!
Stay blessed sir
Thanks so much.
Hello sumit.. i am your no. 1 fan, can you make tutorial of the difference between Row, Rows, column, columns, Cell, Cells, worksheet, worksheets. etc .. thanks you so much
Very good video. Thank You.
Excellent Video
Your explanation is superb. No one can explain these topics so easily. I've recently come across this problem. Sir, would you kindly please make a video on this topic - "How to split Strings that contain no Delimiter using User Defined Function in Excel VBA?" For Example, - You have some client names on your Sheet1 from Range("A2") to Range("A16"):
Raw Data:
========
Names First Names | Middle Names | Last Names
------------ -------------------- ------------------------ --------------------
AlexanderWilliamJamesBrown
AmandaTaylor
AriaNguyen
AubreyFoster
AveryAnnPhillips
Beerus
BenjaminThomasHarris
CharlotteMitchell
ChristopherJackson
DanielJamesMiller
DavidAndrewJohnWilliams
Dende
DylanJamesWard
ElijahWatson
EllaSophiaRoberts
==========
Here, some clients have First Names, Middle Names and Last Names (For Example: Alexander William James Brown etc.); some have First Names and Last Names (For Example: Amanda Taylor etc.) and some have only First Names (For Example: Beerus etc.). Now, I want to delimitate each name based on the Capital Letter. Desired Output mentioned below:
Desired Output:
=============
Names First Names | Middle Names | Last Names
------------ -------------------- ------------------------ --------------------
AlexanderWilliamJamesBrown Alexander | William James | Brown
AmandaTaylor Amanda | | Taylor
AriaNguyen Aria | | Nguyen
AubreyFoster Aubrey | | Foster
AveryAnnPhillips Avery | Ann | Phillips
Beerus Beerus
BenjaminThomasHarris Benjamin | Thomas | Harris
CharlotteMitchell Charlotte | | Mitchell
ChristopherJackson Christopher | | Jackson
DanielJamesMiller Daniel | James | Miller
DavidAndrewJohnWilliams David | Andrew John | Williams
Dende Dende
DylanJamesWard Dylan | James | Ward
ElijahWatson Elijah | | Watson
EllaSophiaRoberts Ella | Sophia | Roberts
Thank you, Sir, for uploading the video on my request. A few years ago, I commented this question to you. Y
Nice video
Hi great videos and crystal clear
Could please 🙏 solve the this error
Dim j as variant,
J= Array(cells(1, 10), cells(1, 20)).
When watch this code in local window getting typescript error could please help me out of error.
Thanx
Thanks Sumit. This is awesome! I have a question though. You used two separate loops. One for reading the data from the sheet and another for writing the data to a different area. Couldn't you have done both in the same loop?
i know I'm quite randomly asking but do anybody know of a good place to watch new tv shows online?
Good but you should make a video about the functions before you use it (e.g redim)
good 👍👍👍👍
sir you great but if you are indain please take hindi audio for indian please 🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏
I am unable to pay you from India. I have some queries on array. How can I contact you?
In the offset u mention it is not clear where the negative sign comes from. If we use 1,0 then 2,0 etc so why do u write -1 ? May please clarify this part ? This is also repeated when using offset. dimension 1 and dimension 2, -1 was also used 🤔🤔
Can you please explain how to use array of array in VBA? this is my logic. i have multiples arrays that stores rows value like arrRow1 ,arrRow2 ,arrRow3 so now i want to store these arrays in a one array that is arrTable[arrRow1 ,arrRow2 ,arrRow3]. If I want to access any value then I'll use
arrTable[0][1]:- 0 for arrRow1 and the one for the arrRow[1] how its possible in Vba.
Sir, is Vba still in demand in 2020?
sir, I have some doubts. How can I reach you?
Well explained, however the graphics are barely legible.
In minutes 10 u used offset (counter -1,0) . Why dit u use -1. It is not clear really?
here range ("a2") is activecell and
In offset (x,y) here x = 1,y=1
means lower bound element start from 1
Then range ("a2"). offset (1,1). value will be store in range ("b3") as "Sinopec"
so this will be wrong
Now here we want to store value in range ("a2") then we need (-1)
bcz here lower bound element start from 1 not from 0
If lower bound element from 0 then
No need minus1(-1)
Sub arrays()
Dim names() As String
Dim dimensions1 As Long
Dim j As Long
dimensions1 = Range("a1", Range("a1").End(xlDown)).Cells.count
ReDim names(1 To dimensions1)
For j = 1 To dimension1
names(j) = Range("a1").Offset(j - 1, 0).Value
Next j
End Sub
sir i am face very problem to solve this dyanamic array please help me
please help to solve this problem
@@ishrafilansari2241 the only thing wrong in this code is that you have not used proper variable in the line For j = 1 To dimension1 it should be For j = 1 To dimensions1 you were missing an "s" just before the Dimensions.