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

Maybe you could trust excel to return the lastused cell's row. I find that this
is accurate if you have removed any data.

dim LastRow as long
lastrow = activesheet.cells.specialcells(xlcelltypelastcell) .row

And if you've already deleted rows, excel will remember the last usedcell--which
is probably different than you want.

Sometimes just using the .usedrange property will refresh excel's memory.

dim Lastrow as long
dim DummyRng as range
'lots of code doing lots of things
set dummyrng = activesheet.usedrange
lastrow = activesheet.cells.specialcells(xlcelltypelastcell) .row


Debra Dalgleish has a routine that you may like better at:
http://www.contextures.com/xlfaqApp.html#Unused


Jim wrote:

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

--

Dave Peterson