Going Backwards: How Not to go past beginning of file

I have a macro that works from the bottom of the file towards the top. A series of for/next loops copy and paste data in 100 line chuncks starting at the bottom. Works great until it gets 99 lines from top of file then it crashes.

Here's the VBA that crashes:

ActiveCell.Offset(-101, 0).Range("A1").Select

I tried
If BOF = true then end
elseif
ActiveCell.Offset(-101, 0).Range("A1").Select
end if

but that didn't work.

any ideas?

Rob

Rob

It's not suprising it crashes - if you are on row 99 and offset -101 then you'll be trying to select row -2 (or something like that).

Why not check the row of the ActiveCell before the offset?

Please Login or Register  to view this content.

thanks for the idea. i cannot get it to work because of the position of the activecell.offset statement in the for next loop. here's the whole macro.

Sub Macro3()
' Macro3 Macro
ActiveCell.Columns("A:B").EntireColumn.Select
rowcnt = Worksheets("sheet1").Range("A:B").Rows.Count
Selection.SpecialCells(xlCellTypeLastCell).Select
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
For i = 1 To 100
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Next
ActiveCell.Offset(-101, 0).Range("A1").Select
For j = 1 To rowcnt
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
For k = 1 To 100
ActiveCell.Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Insert Shift:=xlDown
Next
ActiveCell.Offset(-101, 0).Range("A1").Select
Next
End Sub

Any variation i could think of on an if activecell=100 caused a loop at that point that seemed infinite.

Suggestions?

Can you explain exactly what the code is meant to do?

By the way you don't need to select/activate cells and ranges to work with them.