Hello,

I have some code that adds the contents of one sheet to the end of
another. How do I correctly select the first empty cell below the first
sheet that I want to add data to?

Something like

ActiveCell.SpecialCells(xlLastCell).Select

'Move to bottom of range

^^^ This is where I need work.

Can I simply perform a cursor key move down one cell?

How do I code cursor key operations?

Hi,

It is very unlikely that you will need to select a cell to do what you want
but here's a couple of methods

Select empty cell after last used cell in col A
Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select

Select empty cell in Col A below the last use cell on worksheet
Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Copacetic" wrote:

Hello,

I have some code that adds the contents of one sheet to the end of
another. How do I correctly select the first empty cell below the first
sheet that I want to add data to?

Something like

ActiveCell.SpecialCells(xlLastCell).Select

'Move to bottom of range

^^^ This is where I need work.

Can I simply perform a cursor key move down one cell?

How do I code cursor key operations?
.

See also
http://www.rondebruin.nl/last.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm



"Mike H" wrote in message ...
Hi,

It is very unlikely that you will need to select a cell to do what you want
but here's a couple of methods

Select empty cell after last used cell in col A
Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select

Select empty cell in Col A below the last use cell on worksheet
Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Copacetic" wrote:

Hello,

I have some code that adds the contents of one sheet to the end of
another. How do I correctly select the first empty cell below the first
sheet that I want to add data to?

Something like

ActiveCell.SpecialCells(xlLastCell).Select

'Move to bottom of range

^^^ This is where I need work.

Can I simply perform a cursor key move down one cell?

How do I code cursor key operations?
.

Select empty cell after last used cell in col A
Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select

The above can be shortened to this...

Cells(Rows.Count, "A").End(xlUp).Offset(1).Select


Select empty cell in Col A below the last use cell on worksheet
Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select

As Ron points out in the article he linked to, SpecialCells(xlLastCell) can
give you the wrong cell reference under certain circumstances. If you are
looking for a second method, though, then here is an alternative which will
work...

Columns("A").Find("*", , xlFormulas, , xlRows, xlPrevious).Offset(1).Select


It should be noted that both of the methods I posted find the empty cell
after the last cell with a value or formula EVEN IF that formula is
displaying the empty cell. If one would want to find the empty **looking**
cell (that is, the cell that is either empty or displaying the empty string)
located after the last displayed, non-empty value, you could use this last
method using xlValues in place of xlFormulas...

Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Offset(1).Select


--
Rick (MVP - Excel)



"Mike H" wrote in message
...
Hi,

It is very unlikely that you will need to select a cell to do what you
want
but here's a couple of methods

Select empty cell after last used cell in col A
Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select

Select empty cell in Col A below the last use cell on worksheet
Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Copacetic" wrote:

Hello,

I have some code that adds the contents of one sheet to the end of
another. How do I correctly select the first empty cell below the first
sheet that I want to add data to?

Something like

ActiveCell.SpecialCells(xlLastCell).Select

'Move to bottom of range

^^^ This is where I need work.

Can I simply perform a cursor key move down one cell?

How do I code cursor key operations?
.

What I am after is placing the cursor in the desired cell of the
desired sheet so that when I copy and paste to it, I am certain of the
location of the paste.

What I do is open a three sheet 2k3 format workbook and delete the
first row of all three, then save it in 2k7 format so I can increase the
data set size, then close and re-open that to allow it to actually
function in 2k7 mode, then move the cursor to the end of the first sheet,
then mark and copy the second, and paste it into the first at that end
point, the repeat with the third, then delete the second and third, then
save again

My current code fails because being from a recorded macro, it carries
the direct cell reference instead of my cursor moves to get to the end of
the current data set before I paste, and that does not work when I open a
file that has more records in it than the previous had (or less).

My current code uses the last suggestion I got in this thread before,
and is untried, but here 'tis, see if you can see any easier way. The
database itself is at:

http://www.hometheaterinfo.com/download/dvdlist.zip

It shrinks from 46 MB to 19 MB after I do this conversion. It also
makes it easier to query and use as a flat file database.

The macro is quoted below:

Sub BookMorpher()
'this sub opens the downloaded 2k3 file, saves it as a 2k7 format,
'then concatenates the contents of two segmented sheets of data
'onto the tail of the first sheet, then deletes the two segments

' Open the downloaded 2ks version file
Workbooks.Open Filename:="C:\DVD_Image_Database\temp\dvdlist.xls"
Sheets("a-f").Select
'rename the first sheet
Sheets("a-f").Name = "DVDs"
'shift the ID column to the correct location for all three sheets
Columns("N:N").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Sheets("g-o").Select
Columns("N:N").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Sheets("p-z").Select
Columns("N:N").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
'delete the top row of all three sheets
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Sheets("g-o").Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Sheets("DVDs").Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp
'Move to the end of the DVDs sheet
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select
'Save as then re-open the 2k7 file format workbook
'this is required as the saved-as sheet will not
'accept the pastes until it has been closed and re-opened
ActiveWorkbook.SaveAs Filename:="C:\DVD_Image_Database\dvdlist.xlsx",
_
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Workbooks.Open Filename:="C:\DVD_Image_Database\dvdlist.xlsx"
'Copy the segmented sheets to the end of the first sheet
Sheets("g-o").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DVDs").Select
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select
Sheets("p-z").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DVDs").Select
ActiveSheet.Paste
'delete the un-needed segmented sheets, leaving only the primary data set
Sheets("p-z").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("g-o").Select
ActiveWindow.SelectedSheets.Delete
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

On Sun, 18 Apr 2010 10:42:03 -0400, "Rick Rothstein"
wrote:

Select empty cell after last used cell in col A
Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select

The above can be shortened to this...

Cells(Rows.Count, "A").End(xlUp).Offset(1).Select


Select empty cell in Col A below the last use cell on worksheet
Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select

As Ron points out in the article he linked to, SpecialCells(xlLastCell) can
give you the wrong cell reference under certain circumstances. If you are
looking for a second method, though, then here is an alternative which will
work...

Columns("A").Find("*", , xlFormulas, , xlRows, xlPrevious).Offset(1).Select


It should be noted that both of the methods I posted find the empty cell
after the last cell with a value or formula EVEN IF that formula is
displaying the empty cell. If one would want to find the empty **looking**
cell (that is, the cell that is either empty or displaying the empty string)
located after the last displayed, non-empty value, you could use this last
method using xlValues in place of xlFormulas...

Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Offset(1).Select

Man, when you guys abandon a thread, you really never look back.


On Sun, 18 Apr 2010 11:27:00 -0700, Copacetic
wrote:

What I am after is placing the cursor in the desired cell of the
desired sheet so that when I copy and paste to it, I am certain of the
location of the paste.

What I do is open a three sheet 2k3 format workbook and delete the
first row of all three, then save it in 2k7 format so I can increase the
data set size, then close and re-open that to allow it to actually
function in 2k7 mode, then move the cursor to the end of the first sheet,
then mark and copy the second, and paste it into the first at that end
point, the repeat with the third, then delete the second and third, then
save again

My current code fails because being from a recorded macro, it carries
the direct cell reference instead of my cursor moves to get to the end of
the current data set before I paste, and that does not work when I open a
file that has more records in it than the previous had (or less).

My current code uses the last suggestion I got in this thread before,
and is untried, but here 'tis, see if you can see any easier way. The
database itself is at:

http://www.hometheaterinfo.com/download/dvdlist.zip

It shrinks from 46 MB to 19 MB after I do this conversion. It also
makes it easier to query and use as a flat file database.

The macro is quoted below:

Sub BookMorpher()
'this sub opens the downloaded 2k3 file, saves it as a 2k7 format,
'then concatenates the contents of two segmented sheets of data
'onto the tail of the first sheet, then deletes the two segments

' Open the downloaded 2ks version file
Workbooks.Open Filename:="C:\DVD_Image_Database\temp\dvdlist.xls"
Sheets("a-f").Select
'rename the first sheet
Sheets("a-f").Name = "DVDs"
'shift the ID column to the correct location for all three sheets
Columns("N:N").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Sheets("g-o").Select
Columns("N:N").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Sheets("p-z").Select
Columns("N:N").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
'delete the top row of all three sheets
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Sheets("g-o").Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Sheets("DVDs").Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp
'Move to the end of the DVDs sheet
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select
'Save as then re-open the 2k7 file format workbook
'this is required as the saved-as sheet will not
'accept the pastes until it has been closed and re-opened
ActiveWorkbook.SaveAs Filename:="C:\DVD_Image_Database\dvdlist.xlsx",
_
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Workbooks.Open Filename:="C:\DVD_Image_Database\dvdlist.xlsx"
'Copy the segmented sheets to the end of the first sheet
Sheets("g-o").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DVDs").Select
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select
Sheets("p-z").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DVDs").Select
ActiveSheet.Paste
'delete the un-needed segmented sheets, leaving only the primary data set
Sheets("p-z").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("g-o").Select
ActiveWindow.SelectedSheets.Delete
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

On Sun, 18 Apr 2010 10:42:03 -0400, "Rick Rothstein"
wrote:

Select empty cell after last used cell in col A
Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select

The above can be shortened to this...

Cells(Rows.Count, "A").End(xlUp).Offset(1).Select


Select empty cell in Col A below the last use cell on worksheet
Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select

As Ron points out in the article he linked to, SpecialCells(xlLastCell) can
give you the wrong cell reference under certain circumstances. If you are
looking for a second method, though, then here is an alternative which will
work...

Columns("A").Find("*", , xlFormulas, , xlRows, xlPrevious).Offset(1).Select


It should be noted that both of the methods I posted find the empty cell
after the last cell with a value or formula EVEN IF that formula is
displaying the empty cell. If one would want to find the empty **looking**
cell (that is, the cell that is either empty or displaying the empty string)
located after the last displayed, non-empty value, you could use this last
method using xlValues in place of xlFormulas...

Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Offset(1).Select

Give this a go (not tested):

Sub BookMorpher()
Dim DestCell As Range
'this sub opens the downloaded 2k3 file, saves it as a 2k7 format,
'then concatenates the contents of two segmented sheets of data
'onto the tail of the first sheet, then deletes the two segments

Application.ScreenUpdating = False

' Open the downloaded 2ks version file
Workbooks.Open Filename:="C:\DVD_Image_Database\temp\dvdlist.xls"

'Save as then re-open the 2k7 file format workbook
'this is required as the saved-as sheet will not
'accept the pastes until it has been closed and re-opened
ActiveWorkbook.SaveAs Filename:="C:\DVD_Image_Database\dvdlist.xlsx",
_
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Workbooks.Open Filename:="C:\DVD_Image_Database\dvdlist.xlsx"

'shift the ID column to the correct location for all three sheets
With Sheets("a-f")
.Name = "DVDs"
.Columns("N:N").Cut
.Columns("A:A").Insert Shift:=xlToRight
.Rows("1:1").Delete Shift:=xlUp
End With
With Sheets("g-o")
.Columns("N:N").Cut
.Columns("A:A").Insert Shift:=xlToRight
.Rows("1:1").Delete Shift:=xlUp
End With
With Sheets("p-z")
.Columns("N:N").Cut
.Columns("A:A").Insert Shift:=xlToRight
.Rows("1:1").Delete Shift:=xlUp
End With

Sheets("DVDs").Select
Set DestCell = Range("A1").End(xlDown).Offset(1, 0)

'Copy the segmented sheets to the end of the first sheet
With Sheets("g-o")
.Range("A1", .Range("A1").End(xlDown)).Copy Destination:=DestCell
End With

Set DestCell = Range("A1").End(xlDown).Offset(1, 0)
With Sheets("p-z")
.Range("A1", .Range("A1").End(xlDown)).Copy DestCell
End With

'delete the un-needed segmented sheets, leaving only the primary data
set
Application.DisplayAlerts = False
Sheets("p-z").Delete
Sheets("g-o").Delete
Application.DisplayAlerts = True

'Range("A1", Range("A1").End(xlDown)).Select
Application.ScreenUpdating = True
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

Regards,
Per

On 20 Apr., 04:22, Copacetic
wrote:
Man, when you guys abandon a thread, you really never look back.

On Sun, 18 Apr 2010 11:27:00 -0700, Copacetic



wrote:
*What I am after is placing the cursor in the desired cell of the
desired sheet so that when I copy and paste to it, I am certain of the
location of the paste.

*What I do is open a three sheet 2k3 format workbook and delete the
first row of all three, then save it in 2k7 format so I can increase the
data set size, *then close and re-open that to allow it to actually
function in 2k7 mode, then move the cursor to the end of the first sheet,
then mark and copy the second, and paste it into the first at that end
point, the repeat with the third, then delete the second and third, then
save again

*My current code fails because being from a recorded macro, it carries
the direct cell reference instead of my cursor moves to get to the end of
the current data set before I paste, and that does not work when I open a
file that has more records in it than the previous had (or less).

My current code uses the last suggestion I got in this thread before,
and is untried, but here 'tis, see if you can see any easier way. *The
database itself is at:

http://www.hometheaterinfo.com/download/dvdlist.zip

*It shrinks from 46 MB to 19 MB after I do this conversion. It also
makes it easier to query and use as a flat file database.

The macro is quoted below:

Sub BookMorpher()
'this sub opens the downloaded 2k3 file, saves it as a 2k7 format,
'then concatenates the contents of two segmented sheets of data
'onto the tail of the first sheet, then deletes the two segments

' Open the downloaded 2ks version file
* *Workbooks.Open Filename:="C:\DVD_Image_Database\temp\dvdlist.xls"
* *Sheets("a-f").Select
'rename the first sheet
* *Sheets("a-f").Name = "DVDs"
'shift the ID column to the correct location for all three sheets
* *Columns("N:N").Select
* *Selection.Cut
* *Columns("A:A").Select
* *Selection.Insert Shift:=xlToRight
* *Sheets("g-o").Select
* *Columns("N:N").Select
* *Selection.Cut
* *Columns("A:A").Select
* *Selection.Insert Shift:=xlToRight
* *Sheets("p-z").Select
* *Columns("N:N").Select
* *Selection.Cut
* *Columns("A:A").Select
* *Selection.Insert Shift:=xlToRight
'delete the top row of all three sheets
* *Rows("1:1").Select
* *Selection.Delete Shift:=xlUp
* *Sheets("g-o").Select
* *Rows("1:1").Select
* *Selection.Delete Shift:=xlUp
* *Sheets("DVDs").Select
* *Rows("1:1").Select
* *Selection.Delete Shift:=xlUp
'Move to the end of the DVDs sheet
* *Range("A1").Select
* *Range("A1").End(xlDown).Offset(1, 0).Select
'Save as then re-open the 2k7 file format workbook
'this is required as the saved-as sheet will not
'accept the pastes until it has been closed and re-opened
* *ActiveWorkbook.SaveAs Filename:="C:\DVD_Image_Database\dvdlist..xlsx",
_
* * * *FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
* *Workbooks.Open Filename:="C:\DVD_Image_Database\dvdlist.xlsx"
'Copy the segmented sheets to the end of the first sheet
* *Sheets("g-o").Select
* *Range("A1").Select
* *Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
* *Application.CutCopyMode = False
* *Selection.Copy
* *Sheets("DVDs").Select
* *Range("A1").Select
* *Range("A1").End(xlDown).Offset(1, 0).Select
* *ActiveSheet.Paste
* *Range("A1").Select
* *Range("A1").End(xlDown).Offset(1, 0).Select
* *Sheets("p-z").Select
* *Range("A1").Select
* *Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
* *Application.CutCopyMode = False
* *Selection.Copy
* *Sheets("DVDs").Select
* *ActiveSheet.Paste
'delete the un-needed segmented sheets, leaving only the primary data set
* *Sheets("p-z").Select
* *Application.CutCopyMode = False
* *ActiveWindow.SelectedSheets.Delete
* *Sheets("g-o").Select
* *ActiveWindow.SelectedSheets.Delete
* *Range("A1").Select
* *Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
* *ActiveWorkbook.Save
* *ActiveWindow.Close
End Sub

On Sun, 18 Apr 2010 10:42:03 -0400, "Rick Rothstein"
wrote:

Select empty cell after last used cell in col A
Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1).Select

The above can be shortened to this...

Cells(Rows.Count, "A").End(xlUp).Offset(1).Select

Select empty cell in Col A below the last use cell on worksheet
Range("A" & Cells.SpecialCells(xlLastCell).Row + 1).Select

As Ron points out in the article he linked to, SpecialCells(xlLastCell) can
give you the wrong cell reference under certain circumstances. If you are
looking for a second method, though, then here is an alternative which will
work...

Columns("A").Find("*", , xlFormulas, , xlRows, xlPrevious).Offset(1).Select

It should be noted that both of the methods I posted find the empty cell
after the last cell with a value or formula EVEN IF that formula is
displaying the empty cell. If one would want to find the empty **looking**
cell (that is, the cell that is either empty or displaying the empty string)
located after the last displayed, non-empty value, you could use this last
method using xlValues in place of xlFormulas...

Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Offset(1).Select- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

ActiveCell.SpecialCells(xlLastCell).Offset(1,0).Se lect

Or like this:

Range("A1").End(xlDown).Offset(1,0).Select

Regards,
Per

On 18 Apr., 10:23, Copacetic
wrote:
Hello,

*I have some code that adds the contents of one sheet to the end of
another. How do I correctly select the first empty cell below the first
sheet that I want to add data to?

* Something like

* * ActiveCell.SpecialCells(xlLastCell).Select

'Move to bottom of range

* ^^^ This is where I need work.

* Can I simply perform a cursor key move down one cell?

* How do I code cursor key operations?

Right on the money. Thanks.

On Sun, 18 Apr 2010 02:24:34 -0700 (PDT), Per Jessen
wrote:

ActiveCell.SpecialCells(xlLastCell).Offset(1,0).S elect

Or like this:

Range("A1").End(xlDown).Offset(1,0).Select

Regards,
Per

On 18 Apr., 10:23, Copacetic
wrote:
Hello,

*I have some code that adds the contents of one sheet to the end of
another. How do I correctly select the first empty cell below the first
sheet that I want to add data to?

* Something like

* * ActiveCell.SpecialCells(xlLastCell).Select

'Move to bottom of range

* ^^^ This is where I need work.

* Can I simply perform a cursor key move down one cell?

* How do I code cursor key operations?

In case you selected the SpecialCells version to use, make sure you read the
article that Ron linked to in his posting to see why that might not be a
good idea.

Per's second method will work fine as long as there are **no** blank cells
between A1 and the last piece of data in Column A.

--
Rick (MVP - Excel)



"Copacetic" wrote in message
...

Right on the money. Thanks.

On Sun, 18 Apr 2010 02:24:34 -0700 (PDT), Per Jessen
wrote:

ActiveCell.SpecialCells(xlLastCell).Offset(1,0). Select

Or like this:

Range("A1").End(xlDown).Offset(1,0).Select

Regards,
Per

On 18 Apr., 10:23, Copacetic
wrote:
Hello,

I have some code that adds the contents of one sheet to the end of
another. How do I correctly select the first empty cell below the first
sheet that I want to add data to?

Something like

ActiveCell.SpecialCells(xlLastCell).Select

'Move to bottom of range

^^^ This is where I need work.

Can I simply perform a cursor key move down one cell?

How do I code cursor key operations?

"Per Jessen" wrote in message
...
ActiveCell.SpecialCells(xlLastCell).Offset(1,0).Se lect

Or like this:

Range("A1").End(xlDown).Offset(1,0).Select

Regards,
Per

On 18 Apr., 10:23, Copacetic
wrote:
Hello,

I have some code that adds the contents of one sheet to the end of
another. How do I correctly select the first empty cell below the first
sheet that I want to add data to?


sheet that has to have data [rows] added usualy need to have some known
column structure, otherwise it is mismatched, wrong, invalid

i use framework something like this:

----
row=1
bad=0
lastrow=0
do while bad<100 'max number of bads in sequence
if not valid_row(row) then 'function to test row validity/emptyness
bad=bad+1
else
bad=0
lastrow=row
end if
row=row+1
loop
-----

does such a test make sense, or is just time & cpu vasting?