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

Hi jim_0068,

I've had a similar problem with the SpecialCells method. I forget the
details.
My solution is to loop through the rows starting at LastRw up to row 1
(you might want to change the 1 to what ever top row you want to loop
to)
Test the following out on a backup copy of your data....


Public Sub DeleteRowBBlank()
Dim iRowCounter As Long
Dim Lastrw As Long
Lastrw = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
For iRowCounter = Lastrw To 1 Step -1
If Cells(iRowCounter, 2) = "" Then
Cells(iRowCounter, 2).EntireRow.Delete
End If
Next
End Sub

Ken Johnson

FWIW:
yeah, I think I remember something about SpecialCells
"crapping-out" with a thousand or so cells to work with...

"Ken Johnson" wrote in message
oups.com...
Hi jim_0068,

I've had a similar problem with the SpecialCells method. I forget the
details.
My solution is to loop through the rows starting at LastRw up to row 1
(you might want to change the 1 to what ever top row you want to loop
to)
Test the following out on a backup copy of your data....


Public Sub DeleteRowBBlank()
Dim iRowCounter As Long
Dim Lastrw As Long
Lastrw = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
For iRowCounter = Lastrw To 1 Step -1
If Cells(iRowCounter, 2) = "" Then
Cells(iRowCounter, 2).EntireRow.Delete
End If
Next
End Sub

Ken Johnson

Hi Jim,
If you read Ron's info you will see it "craps-out" big time in a
particularly unamusing way:-)
Ken Johnson

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" wrote in message
...

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.


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

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.Cou nt

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" wrote in message
...

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!


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

Hi Jim

Buy this book
http://www.amazon.com/gp/product/076...oks&v=glanc e

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" wrote in message
...

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

Jim, try something like the following. This builds up the selection
with all the blank rows you wish to delete and deletes them all at
once.

Sub DeleteBlankRows()
Dim lastRow As Long
Dim firstRowFound As Boolean

firstRowFound = False

' last row with data in it on this sheet
lastRow = ActiveSheet.UsedRange.Rows.Count

For i = 1 To lastRow
' the condition on which to decide to delete this row
If IsEmpty(Range("B" & i)) Then
If firstRowFound = True Then
' first time finding a blank row, then select it...
Rows(i).Select
firstRowFound = False
Else
' ... otherwise add it to the selected rows
Union(Selection, Rows(i)).Select
End If
End If
Next i

' delete the selection
Selection.Delete shift:=xlShiftUp

' select only one cell
ActiveCell.Select
End Sub