How to delete multiple rows

  • Thread starter Thread starter bazish
  • Start date Start date
B

bazish

i have data export from crystal report 8 .. and i have data in excel
after every one row blank
i want to delete all blank rows so that i can control space
how can i delete all rows in just few clicks
i have 4387 rows where i have data
and after every row is blank
that is...


1 some thing on it
2
3 some thing on it
4
5 some thing on it
6
7 some thing on it
8
9 some thing on it

and go on and on

i want to delete 2 , 4 , 6 , 8 .. go on .. rows at the same time

i hope you people understand my this little problem

i can delete rows by selcting them all and then delete .. but i want
something other way.. if there is please tell me



------------------------------------------------




------------------------------------------------
 
Select the entire range and then do Edit / Go To / Special / Blanks, then do Edit / Delete /
Entire Row.
 
The other way to do it is via a macro. It could also be that you had blanks in your blank rows as
a result of a formula, in which case the other method I gave you would not work. For these cases,
something like the following would do the trick. You just change the Column to whatever you
need:-

Sub DeleteRows()

Dim lastrw As Long
Dim Rng As Range
Dim Cel As Range

Application.ScreenUpdating = False

lastrw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A"))

For x = Rng.Rows.Count To 1 Step -1
For Each Cel In Rng.Rows(x)
If Cel.Value = "" Then
Cel.EntireRow.Delete
End If
Next Cel
Next x

Application.ScreenUpdating = True

End Sub
 
LOL - You give me a bit too much credit there - Lots of smarter people in here than me.

My email address is as posted, but you simply need to take the NOSPAM bit out of it. That having
been said, it is preferable to post any questions you have in open forum so that all can benefit
from any answers given. Occasionally when it warrants it, a respondent may invite you to send him
a workbook because it is easier to fix that way. Other than that you should try and avoid direct
mail without an invite.

You might want to take a look at Chip Pearson's guide to new posters:-

http://www.cpearson.com/excel/newposte.htm
 
Back
Top