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

www.ExcelVBASql.com


This macro uses a loop to delete blank rows.

Category:

Education

Tags:

• PropertiesWindow
• excel
• macro
• automation
• computer
• office work
• office
• work
• job
• loop
• delete blank cells
• cells
• range
• format
• access
• sql
• sub
• routine
• dim
• variable
• computer programming
• computer science
• excel help
• vba help
• microsoft excel 2007
• training
• tutorial
• Debug
• Toolbar
• Microsoft
• speed
• up
• Object
• Model
• Oriented
• Programming
• OOP
• Objects
• Classes
• VBA
• VB
• Class
• programming
• educational
• microsoft
• vba

[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.

[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!

[IvaFuto]

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

[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

[MarinaInCali1]

@ExcelVBAHelp Ah XD Thanks will do

[raviom15]

Great help...Thank you