VBA Lesson 3 - Deleting blank rows - ExcelVBASql.com 25+ hours of FREE training!

[powerdriller10]

Also, as an example of the power of VBA the video helps a lot. But, in practice, the whole elimination of the row is going to destroy cell formulas and data in cells outside the range. An unacceptable side effect. The real solution would be to scan the range and push up the un-scanned portion every time a blank is found, something like 1- go down a cell 2-select the range from there downwards 3- Cut Ctrl-X. 4-up a cell 5- Ctrl-V Paste

[powerdriller10]

Great didactic material ! Although, I was a kind of disturbed the first time I watched the video by the x vs xrow name mistake which was obvious. I think that the author could prepare the material in two phases, one to develop it and another to polish, so the presentation seem seamless. But maybe the mistakes by being corrected in the video become also great didactic material themselves.

[fitwithjon]

Thanks!

I found this code that works nicely. It deletes any blank rows in the first column.

Sub delete_rows() Columns(1).SpecialCells(xlCell­TypeBlanks).EntireRow.Delete

End Sub

[chintumobile]

hi please help me..i have tried this in excel 2007 and its working...but after deleting rows the macro doesn't stop...its keep running and due to that i have to close the workbook....it seems that its trying to delete all the blank rows afte xlastrow...please help.

[ExcelVBAHelp]

@chintumobile HI please send an email to ExcelVbaSql [at] gmail [dot] com

[MarinaInCali1]

okay believe it or not I tried this from cell c65000 it doesn't work When I try from cell a65000 like in the video, it DOES work like a charm...Why is that?

[ExcelVBAHelp]

@MarinaInCali1 Did you change Cells(xrow,1).value to Cells(xrow, 3).value? i.e. the column needs to change from 1 to 3.

[MarinaInCali1]

@ExcelVBAHelp Ah XD Thanks will do

[raviom15]

Great help...Thank you

[shanthamurthy1234]

Thanks you, This Video is Very Informative.

[IvaFuto]

Thanks for sharing, it's easy to follow, great job!

[ashok442002]

thank you good one but little confusing due to ...... watch the video to know it

[caenies]

Parceros muchas gracias, gracias a este tipo de videos tengo un excelente trabajo, estoy haciendo mas dinero en menos tiempo, soy mas eficiente en mi labor y lo mejor de todo tengo mas tiempo para mi ya que dejo que las maquinas trabajen y no tuve que gastar un dolar en el aprendizaje. hey PAL thanks a lot.

[caenies]

Parceros muchas gracias, gracias a este tipo de videos tengo un excelente trabajo, estoy haciendo mas dinero en menos tiempo, soy mas eficiente en mi labor y lo mejor de todo tengo mas tiempo para mi ya que dejo que las maquinas trabajen y no tuve que gastar un dolar en el aprendizaje. hey PAL thanks a lot.

[profaisnol]

AWesome



[Because011]

Do you have any Video where You have have created a macro to Use Vlookup/Match between 2 workbook.

Thanks

[ExcelVBAHelp]

@Because011 I will put up a video soon about using vlookups in code. Subscribe and stay tuned!

[Because011]

Thank you so much. I have search and search to get what I want and you did if for me.

A+++++++++++++++++

[mveeramurali]

superb,,,,

[kaptkirk7]

Good presentation..in my practice line;

Range("a65000").End(x1up).Sele­ct

keeps hosing when I step through the code. I'm using EXCEL 2003, how can I correct this?

[ExcelVBAHelp]

@kaptkirk7 -- try .End(xlup).Select NOT "x1"

[MrKevin010]

Very nice! like how you have errors and explain why...

Is there a way to download the spread sheet (For us lazy people) so we can play with it?

Again very good, I want to do the same thing but activate the cell and perform a function 10 cells over for every incident that occurs i.e. the word DOG.... THANKS.

[samolee1103]

Thanks a lot,  it is very useful

[almalhoum]

thanks man, this sa big help

[nadirvirk]

Really Good! appreciate!

[gateway123qwert]

Very well explained

[ducksaj]

Hi, could plz help me, I am trying to create a macro to run automatically everytime I change the cells value like " if range ("E7") = "cnh" then" I want to execute the macro if E7 = "cnh" or else exit sub. I hope you have understood me, thank you so much

[MrAvv433247]

Nice tut!!!!

[tbeszcz]

Thanks Dude, I'm just starting with VBA macros and those tutorials are a great help. Apreciate it !

[Joeykaos]

Great piece of code, I used a different method but yours is more efficient. Thank you!

[ROB4MAY]

Like the tutoral

but in my 2007 i get and error at second round on this line ;If Cells(xrow, 1).Value = "" Then

Can you help me get past this??

[nagendrajayanty]

Thanks a lot for the demo... and for your information... dim represents dimension if i am not wrong!!

[ellennetube]

An improvement to the readability of the code could be :

xlastrow = Range("a65000").End(xlUp).Row

In this way the value of this variable is 14 in your case and you can easily. If you set a viewer on your variable its value is -1 and in my case instead it is 14

[dnejeff]

Good Job on the Video. I appreciate it!

[shvanvasu]

very nice and understandable and Video, audio quality also too good

[waltzip2009]

very nice.... i have learned a lot dude!

[andoski99]

Great video... I am using this right now and I can see myself using it quite frequently.

Thanks again.

[obesechicken13]

I don't think you need the ".End(x1Up)" in 2007

[obesechicken13]

@obesechicken13 erhh, sorry, you do. .end(x1up) chooses the last row in the 65000

[xsmail321]

very nice. do you have a website where people can practice? or any recommendations for a website to practice exercises? .. also what about deleting blank columns?

[caruv28]

Really Thanks!

[bujika10]

great

[Smartman1023]

Thanks for the video......Good one

[anagav28]

Excellent Video

Thanks :)

[brlinga]

thanks

[excel201013]

Hello Thank you very much for this video. All your lessons are very good and giving a very good understanding about VBA.

[chilenosueco]

gracias!

[lezenderv280]

very much helpful for the beginner.

very good video.

[loayuve]

Hey, can some1 explain to me these 2 codes

Range("a65000").End(x1up).sele­ct

xlastrow = ActiveCell.Row

By the way, this is a great video !

[armsoo]

Hi, I will try to explain it:

First line is to identify the last cell with a value in it and selecting it.

second line is to define that selected cell from previous line as "xlastrow"

You need these two lines so it will understand that it should go do this loop until you reach your last row, if not then it will not work as it will not know what is the xlastrow and wont understand where the process ends.

(I guess, as I am new to VBA and just learning it)

[boxerhopia]

clear presentation. thanks!

hence, i know there's a lot to learn about the for/next; do until and do while statement especially when setting up conditions.

hope you wouldn't mind if i'll be sending out a follow up question soon...

[devidkennedy]

wonderful.

thank you

[rahulin]

Thanks

[russi112]

great ..vdo..helped me a lot

[pgmking]

don't worry I worked it out....lol I hadn't put a space between the "".

[actuary2011]

Thank you!