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
>