Hi Guys,

I have made a macro which imports data from a text file. The macro
works pretty sweetly and sorts data and then removes various rows
which I dont want via functions of CountIf and CurrentRegion.

Herein lies my problem. The last few rows of my data look like:


A B C D E F
99 ABC DEF HIG LMO PQR STU
101 *** VEH REP ***
102 *** 23/11 03 ***
103 *** ***** ** ***
104
105 DIST1
106
107 DIST2
108
109 DIST3

Current Region only selects down as far as the last row of "*" because the
next line is entirely blank.

I could just calculate end of current region and make a selection where the
start
row is "-4" and end row is +1000 (to be safe) but this is rather ugly and I
like
to have robust code.

I guess what I need is something to find the first blank row in column A
then
delete rows until it gets two consecutive blank rows, then stop?

Anyone help?

Cheers

-Al

This is one method

Sub abc()
Dim i As Integer
For i = Range("a65536").End(xlUp).Row To 1 Step -1
If Range("a" & i).Value = "" Then
Rows(i).Delete Shift:=xlUp
End If
Next i
End Su

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Instead of using Current Region to find the range with data you can use
UsedRange, e.g.:

ActiveSheet.UsedRange.Select

If you still want to delete the empty rows a quick and easy way is to copy
the rows with data to a new sheet like this:

Union(ActiveSheet.UsedRange.SpecialCells(xlConstan ts).EntireRow,
Rows(1)).Copy
Worksheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False


--
Jim Rech
Excel MVP

Thanks guys, I will go through that code and check it out.

I have also found that when I go to run the code on Office 97 (it was
written
in 2003) their are a few lines that need tweaking to work, most of which I
have
fixed but I am stuck with this;

Error 91
Object variable with block variable not set

Code breaks at line:

Set sh = Sheets(1)

Set rng = sh.[a1].CurrentRegion

Tag = "PAGE:"

num = Application.CountIf(rng, Tag)

startrow = rng.Find(What:=Tag).Row

MsgBox startrow.Row

endrow = startrow + num

Range(rng.Rows(startrow), rng.Rows(endrow - 1)).Delete <-----error occurs
on this line



Anyone know why?



-Al



"Jim Rech" wrote in message
...
Instead of using Current Region to find the range with data you can use
UsedRange, e.g.:

ActiveSheet.UsedRange.Select

If you still want to delete the empty rows a quick and easy way is to copy
the rows with data to a new sheet like this:

Union(ActiveSheet.UsedRange.SpecialCells(xlConstan ts).EntireRow,
Rows(1)).Copy
Worksheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False


--
Jim Rech
Excel MVP

The delete worked fine for me under Excel 97. This line did error though:

MsgBox startrow.Row

That's because startrow is an integer not a range. It's a good idea to
declare your variables and use Option Explicit so you know what each is.

--
Jim Rech
Excel MVP