Комментарии:
Beginner learner here. My code was crude. This is so efficient and logical. Thank you so much for this valuable help.
ОтветитьExcellent, I've learned a lot here Thanks.
Ответитьvery helpful, was struggling with the lookat:=xlWhole for looping through the table but databodyrange solved my issue
ОтветитьThanks for your well explained and relevant video.
At first, I couldn't understand why you decided to loop through the bottom to the top of the Table, so I decided to do the opposite, which I immediately regret of...
When you delete a row, the table rows move one row to the top, so going from the bottom to the top ensures that every row that meets the criteria will be deleted.
Thanks a lot!
That was very help.
Thumbs it is.
Thank you so much for making this tutorial. I was banging my head over listobjects until I saw this.
ОтветитьBruh! I about lost it when you showed you could turn off screen updates...thanks a billion!!!
ОтветитьAs a tutorial on how to index the listobject is okay, but it would worth mentioning that there is a built-in function for queriyng the column index of a given header:
cl = lo.listColums("weight")
If you don't need the column index, just want to loop through the data in that column, instead of
lo.DataBodyRange(cl, i)
lo.listColumns("weight").DataBodyRange(i)
could also be used.
Which is better and faster is depends on the task and your implementation.
There is also a function for searching a given value in a range: Application.Match and Application.WorkseetFunction.Match
It gives you back the matching row without looping through the data.
If it is still not powerful enough, there is also a nuclear weapon: Apllication.Evaluate and Worksheet.Evaluate which will evaluate a complete formula for you, and you might even get an array of all the matches and so on.
Is also worth mentioning, in general, that if there is a built-in function for a task, it usually runs magnitudes faster than reimplementing the same with VBA.
I don't say that there is no reason to loop through the rows, but thats typically to write or manipulate them, less frequently to search, count or similar.
My another recommendation is to learn and implement error handling as much as you learn the others. For example, it is cruical to verify that the data you are looking for is exist even, formulas returned valid data (non-error), the column is found indeed (the user might renamed it). The best case is it just drops you to the debugger, worse is it silently does nothing, the ultimate disaster is when it changes other data than it is supposed to!
What is the datatype of variable "rw" here? Is it a Range or Long? or ListRow?
ОтветитьVery good approach to work on data (ListObject)! Ps. to find a column You could also use WorksheetFunction.Match :)
ОтветитьExcellent video.
ОтветитьThanks a million. Great video.
ОтветитьThank you, very inspiring video
ОтветитьHi. How could I loop through the tables in the database and fill in the name of these tables in an excel spreadsheet?
ОтветитьGreat tutorial, thank you.Short and straight to the point. Well done!
ОтветитьMost of my data in Excel are in tables or listobjects. This tutorial helps a lot. Many thanks. Subscribed!!!
ОтветитьGreat tutorial 👍👍
ОтветитьHi
I need your help
I am making excel VBA code in the Developer tab to create Powerpoint Presentation
I have successfully generated PowerPoint slides using my VBA code in excel
But, I want to set the background as an image. I am unable to do so
In my excel VBA code, I have the variable ppSlide, that is for the newly created Slide
In my excel VBA code, I am running the code
ppSlide.Background.Fill.UserPicture ("Address of the picture that is local to my Laptop hard drive with JPG extension")
Entire code is getting executed, but the background image of my Slide is not getting in the Slide
I am using MS Office 2007
Note : I have already added Microsoft Powerpoint 12.0 Object Library
in my Tools -> References
Kindly help me.