Deleting rows with blank cells

Hello

I've done a lot of searching and things have been helpful but i can't quite seem to tweak the VBA code to do exactly what i want.

I want to delete every row in my worksheet that contains no data; blank cell. I'd like to do this for column B as it will take care of my needs for the entire sheet.

I found this code on the net and it works great BUT it stops and doesn't keep going to the end of the work sheet. Is there a way i can get the code shown below to go through a range of like B12:B20000?

Here is the code, thanks:

With ActiveSheet
LastRw = .Cells(Rows.Count, "b").End(xlUp).Row
Set Rng1 = .Range(Cells(1, "b"), Cells(LastRw, "b"))
End With
With Rng1
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

Hi Jim

See
http://www.rondebruin.nl/delete.htm

Warning: Check out this page
http://www.rondebruin.nl/specialcells.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"jim_0068" <jim_0068.25902m_1143328201.3204@excelforum-nospam.com> wrote in message
news:jim_0068.25902m_1143328201.3204@excelforum-nospam.com...
>
> Hello
>
> I've done a lot of searching and things have been helpful but i can't
> quite seem to tweak the VBA code to do exactly what i want.
>
> I want to delete every row in my worksheet that contains no data; blank
> cell. I'd like to do this for column B as it will take care of my needs
> for the entire sheet.
>
> I found this code on the net and it works great BUT it stops and
> doesn't keep going to the end of the work sheet. Is there a way i can
> get the code shown below to go through a range of like B12:B20000?
>
> Here is the code, thanks:
>
> With ActiveSheet
> LastRw = .Cells(Rows.Count, "b").End(xlUp).Row
> Set Rng1 = .Range(Cells(1, "b"), Cells(LastRw, "b"))
> End With
> With Rng1
> SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> End With
>
>
> --
> jim_0068
> ------------------------------------------------------------------------
> jim_0068's Profile: http://www.excelforum.com/member.php...o&userid=32822
> View this thread: http://www.excelforum.com/showthread...hreadid=526299
>

I actually found that page too.

I'm not much of a programmer or code writer. I don't really know how all that code works on that site. I did try and use it an re-arrange some of the code and change the "range" but it didn't work.

That's why i ended up here.

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA

Try this one for the activesheet


Sub DeleteBlankRows_2()
'This macro delete all rows with a blank cell in Range("B12:B20000")
'If there are no blanks or there are too many areas you see a MsgBox
Dim CCount As Long
On Error Resume Next

With Range("B12:B20000")

CCount = .SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Count

If CCount = 0 Then
MsgBox "There are no blank cells"
ElseIf CCount = .Cells.Count Then
MsgBox "There are more then 8192 areas"
Else
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If

End With

On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"jim_0068" <jim_0068.259itm_1143352500.975@excelforum-nospam.com> wrote in message
news:jim_0068.259itm_1143352500.975@excelforum-nospam.com...
>
> I actually found that page too.
>
> I'm not much of a programmer or code writer. I don't really know how
> all that code works on that site. I did try and use it an re-arrange
> some of the code and change the "range" but it didn't work.
>
> That's why i ended up here.
>
>
> --
> jim_0068
> ------------------------------------------------------------------------
> jim_0068's Profile: http://www.excelforum.com/member.php...o&userid=32822
> View this thread: http://www.excelforum.com/showthread...hreadid=526299
>

Wow, that worked great.

Thanks for the link as well. I'm a financial analyst by profession and i am just starting to really learn how to use excel, VBA, macros, functions etc and it is all very interesting and handy as well.

Where do you think would be the best place to really learn how to use all that excel has to offer. Would you recommend any certifications or books?

Thanks again!

Hi Jim

Buy this book
http://www.amazon.com/gp/product/076...books&v=glance

If you like to know more about Formula's the "Better together" on the page is a great offer

--
Regards Ron de Bruin
http://www.rondebruin.nl


"jim_0068" <jim_0068.25arym_1143411001.8663@excelforum-nospam.com> wrote in message
news:jim_0068.25arym_1143411001.8663@excelforum-nospam.com...
>
> Wow, that worked great.
>
> Thanks for the link as well. I'm a financial analyst by profession and
> i am just starting to really learn how to use excel, VBA, macros,
> functions etc and it is all very interesting and handy as well.
>
> Where do you think would be the best place to really learn how to use
> all that excel has to offer. Would you recommend any certifications or
> books?
>
> Thanks again!
>
>
> --
> jim_0068
> ------------------------------------------------------------------------
> jim_0068's Profile: http://www.excelforum.com/member.php...o&userid=32822
> View this thread: http://www.excelforum.com/showthread...hreadid=526299
>

Hello

The above macro is working great, now i am trying to tweak this code so i can delete more than just blank cells.

I want to delete rows with other words in them too.

What would be the best way to go about doing that? Should i copy the code again and change the parameter from blank to the word i want?

I'm going to keep tinkering with it until i get it but any help would be great.

Just to clarify, i want to delete both blank rows and rows that have words like "part" or "description"

thanks again!

Just wanted to chime in on the book reference, an excellent choice.
Well written, practical examples, detailed information and he really
skips over most of the stuff you should already know if you're buying
this book.

He also includes the entire book in PDF on the CD, very handy to refer
to without having to lug around a 5 pound monster.

Jesse

See my delete page for ideas
http://www.rondebruin.nl/delete.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"jim_0068" <jim_0068.25uz9z_1144353603.5982@excelforum-nospam.com> wrote in message
news:jim_0068.25uz9z_1144353603.5982@excelforum-nospam.com...
>
> Hello
>
> The above macro is working great, now i am trying to tweak this code so
> i can delete more than just blank cells.
>
> I want to delete rows with other words in them too.
>
> What would be the best way to go about doing that? Should i copy the
> code again and change the parameter from blank to the word i want?
>
> I'm going to keep tinkering with it until i get it but any help would
> be great.
>
> Just to clarify, i want to delete both blank rows and rows that have
> words like "part" or "description"
>
> thanks again!
>
>
> --
> jim_0068
> ------------------------------------------------------------------------
> jim_0068's Profile: http://www.excelforum.com/member.php...o&userid=32822
> View this thread: http://www.excelforum.com/showthread...hreadid=526299
>

Book is on order!

hopefully it's not too advanced for me.

Ron

I'm using the Sub Delete_with_Autofilter_Array() version to delete the multiple criteria. However as i am adding criteria to the array i have run into a small issue.

I have a number of things called "order dates: xxxx" in my column and the dates are all different.

How can i tell the array to delete anything that begins with the word "order." If i simply put in the word "order" it doesn't work.

Thanks!

You can use wildcards like
order*

or *order*



--
Regards Ron de Bruin
http://www.rondebruin.nl


"jim_0068" <jim_0068.25v2za_1144358403.0332@excelforum-nospam.com> wrote in message
news:jim_0068.25v2za_1144358403.0332@excelforum-nospam.com...
>
> Ron
>
> I'm using the Sub Delete_with_Autofilter_Array() version to delete the
> multiple criteria. However as i am adding criteria to the array i have
> run into a small issue.
>
> I have a number of things called "order dates: xxxx" in my column and
> the dates are all different.
>
> How can i tell the array to delete anything that begins with the word
> "order." If i simply put in the word "order" it doesn't work.
>
> Thanks!
>
>
> --
> jim_0068
> ------------------------------------------------------------------------
> jim_0068's Profile: http://www.excelforum.com/member.php...o&userid=32822
> View this thread: http://www.excelforum.com/showthread...hreadid=526299
>