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

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

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

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

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

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

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

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

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.


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

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

You can use wildcards like
order*

or *order*



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


"jim_0068" wrote in message
...

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