Excel VBA: Referring to Ranges & Writing to Cells (Range, Cells, Offset, Names)

[@LeilaGharani]

Get access to the full Excel VBA course here 👉 www.xelplus.com/course/excel-vba-excel-macros/

[@TheDutchGemini]

VBA allows to specify ranges using the short notation with square brackets instead of the Range() or Cells() property. For instance 'Range("A1").Value' can be written in short as '[A1].Value'. This is also true when using areas, for instance [A2:C2].Value = "2nd", and named ranges such at [LastOne].Value = "10th".

[@Yolande26]

Leila has the best Excel tutorials/videos by far! I did two of her courses and she is simply the best instructor!

[@LeilaGharani]

Wow, thank you!

[@rnunez047]

In Spanish-language there's not channel like this one. Great, you have a nice voice, even my native language is Spanish, i get all you explain. I just founded you. Congratulations "Desde américa Latina, Nicaragua. Saludos"

[@LeilaGharani]

Thank you! 😃

[@aricosman5354]

GOD bless you Mrs Leila, Thanks a lot for your tutorials

[@mariarib6504]

omg. I'm not getting much sleep. I keep learning more from you and instead of fixing the basic stuff I have written, I am thinking of other things I can do! Thank you :-)

[@mehdihammadi6145]

I am one of those who believe that there is always something to learn. And this has just been verified once again. Although I have been writing VBA code for years, I have never used the shortcut "?" in the immediate window because I used to write the entire debug.print command. So thank you very much Leila for this

[@LeilaGharani]

Hi Mehdi. I'm glad you found something new here. Actually I was also not aware of the "?" method for the longest time after I started to use VBA. It made me very happy when I discovered it :)

[@mehdihammadi6145]

Hi Leila, for the history this is an old (legacy) command coming from old versions of Basic. If my memory is good I used it with gwbasic for more then 25 years ago :)

[@LeilaGharani]

Thanks for the info :)

[@chandrasekharboya8885]

Hi Leila, Really really impressive explanation of cell referencing. This is the best video explaining the cell references. Thank you.

[@PremKumar-pq5lr]

Used this techniq and solved a issue for which i am looking for a solution in long time. Keep rocking😎👏✌

[@rag7628]

Hello Leila, I've been working now through some of your great tutorials here and now I really have to make you a big compliment. Thanks sooooo much for the great work you a doing here, it's really a great performance. Your tutorials are very well structured and very easy to understand and extremely helpful. Thank You :-)

[@ismailismaili0071]

I think I started to understand VBA with you thank you so much Ms. Leila

[@lawrenceward2366]

New to VBA. Most useful video I have seen. Thank you.

[@LeilaGharani]

I'm very happy to hear that Lawrence. Glad you like the video.

[@davecks3042]

I did enroll in Udemy in Leila VBA course and I learned immensely. Thanks

[@teachmeteachme9014]

You're creative, so I've decided to outdo you in this field. تحية لك من المغرب

[@AamirSaeedTajalli]

Lady Leila, I LOVE YOU for making this wonderful tutorial. Excellent TEACHER.. knowing the art of MAKING UNDERSTAND. Thanks a lot.

[@LeilaGharani]

I'm very happy to hear this Aamir! Glad you like the videos.

[@AamirSaeedTajalli]

@@LeilaGharani i started watching your tutorials 3 days back and the more i am diving in, the more i am learning "FAST" and more i am liking your WAY of teaching. Full of confidence, command, knowledge and patience. GREAT WORK and inspiring achievements. Getting convinced to join your online tutorials paid version. I hope they will be even better.

[@dasrotrad]

This is really great Leila. Love your presentation and excellent explanations. It is very well thought out. You make it easy to understand. When Mike Girvin is following your videos, you know you are among the great. Thank you so much for sharing.

[@lilanthawijerathne3085]

Hats off to Leila ! . I learns lot from your videos.

[@LeilaGharani]

Glad to hear that

[@sasavienne]

Cells.Columns.Autofit is lovely. Leila gets 🌟 🌟 🌟 🌟 🌟

[@LuisGarcia-mn4jg]

You explain concepts very well and it is easy to follow. Thanks

[@LeilaGharani]

Glad you think so!

[@abdallah.kandiel]

Thankd leila Back to our happy day Waiting for the end to learn about the course Good luck ur highness

[@LeilaGharani]

You're welcome. It's happy Thursday :) Wish you a great rest of the day.

[@MyChannel706]

Excellent video and narration! Thank you!

[@scorpio19771111]

Excellent video. You are great instructor, and the video is top-notch in clarity.

[@ca.lakshminarayanreddyjamb9087]

I like the way you explain. Really awesome!!! Thankyou.

[@alwysrite]

was looking for multiple range syntax and found it here after looking on various sites. Thank you

[@j.fabricioelias227]

Thank you! This video gave me an excellent explanation about cells and ranges referencing.

[@LeilaGharani]

Glad it was helpful!

[@abumalik97]

impressive teaching ability , right pace and right details . subscribed !.

[@LeilaGharani]

Much appreciated!

[@steamsteam6607]

Such a smart, beautiful woman. Iran makes great things. Thank you for these videos.

[@steamsteam6607]

@Elegant Fowl Pls

[@mobr.]

Clear, concise, and informative. Thank you.

[@ckokse]

Thursday is my favorite day! I was already wating for your new video. This is great, will practice this as you instructed. I want to be prepared for when the VBA course finally comes out.

[@LeilaGharani]

Mine too! Great. Practice is always good :))

[@arielponce8586]

You are an awesome teacher.

[@cobusoosthuizen5223]

Thanks Leila, nice preparation for the upcoming course

[@LeilaGharani]

You're welcome Cobus & thank you.

[@SugarKane9547]

Thanks Leila, You just cleared up a lot of confusion I had about vba range referencing. I am very grateful......and YES, I like what I see but someone already subscribed before me! :)

[@LeilaGharani]

Glad it was helpful!

[@konqueror07]

Awesome video. I think this should be the first video to watch for any one beginning to learn macros like me. I've spent hours trying to figure out how to refer cells in different scenarios. Can you also mention how to refer to variable ranges? Eg Range from A1 to Ai where "i" is a result of another formula or loop counter etc. Thanks for making a video on this topic though.

[@YogeshSharma-ui7xx]

Leila you r awesome and nicely explains with smile and simple way. Thank u

[@ah244895]

Very clear, understandable. Great way to learn Excel m

[@LeilaGharani]

I'm glad to hear that.

[@sanjaykulkarni1800]

Excellent way of explaining.. Thanks.

[@joecampanini8154]

Outstanding presentation. Thank you.

[@pponcho8245]

Thank you for teaching the basics!!!!

[@joem112]

Very good video. I liked the way you did the drag and drop with the named range...pretty cool way to do that.

[@LeilaGharani]

Glad you like that :)

[@marioalfredooviedo]

buenos principios excel, tienes un gran poder de enseñar, hay personas que creen que solo se debe enseñar lo mas astral, pero los principios de excel son excelentes, te felicito

[@LeilaGharani]

Thank you Mario. Glad you like the video.

[@MrWofty]

Oh Wow, solved my problem in a few easy minutes, well explained. I wish my school teachers, College Tutors etc, were like this. Thanks for giving me some enjoyment with learning :) I used this in Excel VBA "A1", "A" & lRow, Where lRow was the last row number. Cheers :)

[@LeilaGharani]

Glad it helped!

[@andydidyouhear]

Great lecture, easy to follow.

[@SanjayKumar-yx6gc]

Very useful lecturer Leila , Thank you so much.

[@LeilaGharani]

You're very welcome Sanjay.

[@Mario112352]

Very nice presentation Look forward to your course

[@cekhattaq]

Explanation is in very nice & systemic way, Thanks

[@LeilaGharani]

Thanks and welcome

[@umeshthadhani6388]

So Clear Explanation....!!! Makes understanding simple.! :) thank you So Much !! :)

[@LeilaGharani]

You are welcome!

[@MrYegneswaran]

Very much insightful.. No need to refer to any other material as far as Range object though VBA..👍👍👍

[@aliz3700]

بهترین استادی👏👏👏

[@sarwansingh9423]

This was very helpful

[@sasavienne]

Brilliant. I like your videos Leila. Thanks a million.

[@LeilaGharani]

Thank you! I’m glad to hear that.

[@stevennye2441]

excellent review!

[@1gopalakrishnarao]

Thanks a lot Madam, very inspiring, illuminating, impressive, inquisitive & revealing, . Explanation is superb, very articulate. On behalf of all my Excel learner friends A BIG SALUTE TO MY BELOVED BEAUTIFUL TEACHER.

[@LeilaGharani]

Thank you very much Gopala for the kind words and for your support. I am glad you're enjoying the VBA series so far :)

[@helenasvensson6595]

So well explained, as always!

[@ringsfieldvillagehallvilla7970]

This is fantastic. I will be taking the course at some point in the future.

[@LeilaGharani]

Great! I hope you'll like it.

[@abdullahalklbani7454]

Amazing 👏 that was full comparhansive explanation 👏 I appreciate that efforts from your side This is was very helpful

[@LeilaGharani]

My pleasure 😊

[@irfanmansoori3060]

Unbeatable knowledge Leila.. thanks..

[@jokkiossaka3306]

Thank You Leila for your time and cool video !

[@LeilaGharani]

My pleasure Jossi. Glad you like the video.

[@yoryopower2908]

Thank you.

[@BOWENSblog]

This is a good hobby

[@alanrobbo6980]

Fantastic, Thanks.

[@Vicky-bl9pr]

You are the best😊😊👌👌👌

[@SolomonKinyanjui_sk]

Hello Leila the lesson to day was very clear. Keep it up.

[@LeilaGharani]

Thank you Solomon. Will do my best :)

[@sindhusudhakaran1731]

so well explained. Thank you

[@excelisfun]

Awesome VBA Referencing video!!!! Quote Happy Fun : )

[@antrikshsharma6990]

ExcelIsFun You should learn VBA as well😁

[@excelisfun]

Yes but where could I find a good class!?!?! ; )

[@LeilaGharani]

Thanks Mike - yes...... not easy to find a good class ;)) In the past week I've been locked up working on finalizing the course. I haven't had a chance to watch your videos, but I've added them all to my list. Have some catching up to do once I get out of here...

[@antrikshsharma6990]

ExcelIsFun Try Wise Owl on youtube👍🏻

[@excelisfun]

Thank you for reaching out to me!!! But I am sorry, antiksh... I was making a joke... I already know about Wise Owl. I feature the Wise Owl channel and Leila's channel at the excelisfun Channel home page : ) I was making a joke that I should take Leila's class. But really it is no joke - she is so amazing at teaching and marketing and making videos : ) Go Amazing Online Excel Team, including you antiksh!!!

[@nour-eddineoumakhlouf5296]

Great job, Leila!

[@LeilaGharani]

Thank you! 😄

[@sarasamin]

Superb! Excellent!Thank YOU!

[@LeilaGharani]

You are very welcome!

[@MohAboAbdo]

Thank You So Mutch.

[@thirdfabe167]

really awesome,,i acquired a lot of knowledge in your video.

[@djibysadji9634]

So clear Leila! Thank you!

[@Galileo2pi]

She is the best

[@RaymondJerome]

thanks, the many ways of getting ranges as part of a row or column is very helpful. all this is similar in openOffice too but they appear to use : and , differently and to also use ;. in VBA i never access activesheet or activerange or selection, I actually pass a range or ranges to the code as in the following which i really have no error checking. Function rms(ByVal Target As Range) As Double 'root mean squared. quadratic mean Dim Cell As Range Dim summand As Double Dim tot As Integer tot = 0 summand = 0# For Each Cell In Target.Cells If IsNumeric(Cell) Then tot = tot + 1 summand = summand + Cell ^ 2 End If Next Cell rms = Math.Sqr(summand / tot) End Function

[@RaymondJerome]

i also use parallel ranges, either rows or columns 1 wide, but have to not use for each

[@mohieddinedadi317]

thank you ; its useful

[@technentertain]

mam i want to enter ony10 numbers in 12 cells in a row. 11th number could not be entered. please help me

[@johnborg5419]

Thanks Leila. Very Interesting. Some practice and will wait for the next one :)

[@LeilaGharani]

You're welcome John. Agree - practice is always good :)

[@cananpasa5623]

Thanks a lot💕💕💕💕💕

[@isa.rahban2132]

thank you very much

[@abhilashc2965]

You are Divine. Demi God of Excel

[@alanliu1313]

Very clear and useful!

[@shoaibahmedsagar]

Thanks a lot Leila.

[@anilpanda5840]

Thanks a lot, very clear!

[@khalidalisubhanallah2947]

Bundle of Thanks Mam Nice Video

[@LeilaGharani]

You're very welcome Khalid. Thank you for your support.

[@jorge56149]

Thanks!

[@satishpatel6530]

Nice video

[@moisestatis5418]

I like how you teach. In fact, I'm enrolled in your Power Query Course. I'd assumed that you also have a Excel VBA course right? I'd like to learn macros.

[@nancyd.5990]

U r the best

[@AamirSaeedTajalli]

Very true

[@Donnie64inPa]

Just figured out my error: I named my module Cells which started this whole mess! (I left my question for anyone else's benefit)>>>>Thanks for the work you put into this video Leila, quick question: When I tried to use your second example: cells(1,1)="1st" it gave me an error: Compile error: Expected variable or procedure, not module. It's like vba isnt recognizing that expression because it's not even suggesting anything when I type the "cells(" part....... Thanks for any help.

[@johnkoay8097]

you are good.

[@shameemnassiree2780]

Thanks ma'am 😊 your videos are quite helpful

[@LeilaGharani]

You're welcome Sami. Glad to hear that :)

[@shameemnassiree2780]

Leila Gharani Ma'am do u have videos on data validation, concatenation ?

[@maryspurplelife2024]

Useful video, Thanks

[@tariquejameel9068]

Can you make video on cutting length optimisation

[@lpanades]

Thanks

[@donserpico8244]

Hey Leila, thanks for your videos. I need to loop through a range (one column) and 1) check if the cell contains a specific text/string and 2) if true, write the text in a new cell (same row). Which of your tutorials do you recommend to watch? Also, I didn't find the next video/lecture you mentioned at the end of this one. thank you in advance

[@souzamotasacul]

Tomorrow, if I remember, I’ll write the code here for you 🤜🏼🤛🏼

[@khalidalisawi8037]

you are great, really it interesting video

[@LeilaGharani]

Thank you! 😃

[@khalidalisawi8037]

@@LeilaGharani

[@khalidalisawi8037]

@@LeilaGharani dear I follow most your videos really it was very interesting and my level is progressing but can you guide me to advanced book in vba

[@mohitdhanwani5540]

Thanks for the video ma'am.

[@LeilaGharani]

You're very welcome Mohit.

[@21121990jay]

Hi @Leila , how would you select two ranges and select them together ? E.g. Header should be fixed Range (First Range) and second range would be from active cell range.

[@DakshaNY]

its good really

[@annpurnakumari7218]

Really awesome. Thanks.

[@robertakwasiadjei8223]

Thanks Leila for such a wonderful video. However, when is the course coming on?

[@LeilaGharani]

You're welcome Robert --- should be live next week :)

[@AliHasan-lr4xj]

Very informative , but I have a request if you can show us how to merge cells than contains same value with option for Excel user to select the cells range as an input on dailog box Thanks

[@fareedbadar8793]

Thank you, for sharing useful information. Please can you advise how you open excel and vbe at the same time?

[@LeilaGharani]

I snap the VBE window in place. You can use the shortcut key window and then right arrow. Or you can drag the VBE window with your mouse and then drag it to the right hand side until it snaps in.

[@fareedbadar8793]

I will try Thank you