PAsting a selection, but only if complete row is empty

Posted by Remi on February 05, 2002 7:58 AM

Hi,

I made a macro which gets information from somewhere and now I want this information to be pasted in a "summary" file. This file gets bigger and bigger so the pasting must take place at the first row that is free.

I am using this piece of programme to do this now:
(to search for the first free row and paste the copied info there)

Sub scanpast()
Dim r As Long
Range("A1").Activate
For r = 1 To 65536
If Not IsEmpty(ActiveCell) Then
ActiveCell.Offset(1, 0).Activate
Else
If IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) And _
IsEmpty(ActiveCell.Offset(0, 2)) And IsEmpty(ActiveCell.Offset(0, 3)) And _
IsEmpty(ActiveCell.Offset(0, 4)) And IsEmpty(ActiveCell.Offset(0, 5)) And _
IsEmpty(ActiveCell.Offset(0, 6)) And IsEmpty(ActiveCell.Offset(0, 7)) And _
IsEmpty(ActiveCell.Offset(0, 8)) And IsEmpty(ActiveCell.Offset(0, 9)) _
Then
ActiveSheet.Paste
ActiveWorkbook.Save

Exit For
Else
ActiveCell.Offset(1, 0).Activate
End If
End If

I got it from somewere and to be honest, I CAN`T FIGURE OUT HOW IT WORKS! Can anybody explain the above mentioned piece of programme to me?

Thanks a lot.

Remi.
Holland.

Posted by Larry Kramer on February 05, 2002 10:18 AM

There are three ways to test whether you have enough room for your data:

1. Test only the first cell in the row if you know that when that cell is empty, the row is empty;

2. Test a number of cells in the row equal to the width of the data you want to paste if an empty first cell does not imply an empty row; or

3. Test the entire row.

This program does #2 by looking at the first cell in the row and the nine cells to its right. Apparently, the author needed 10 empty cells. (The "Offset" functions in your example refers to the cell 0 cells below the active cell and n cells to the right. It's pretty kludgy.)

If an empty first cell implies an empty row, you can delete all the "And" stuff".

You can also say:

range("$a$1).Activate

activesheet.cells(activecell.currentregion.SpecialCells(xllastcell).offset(1,0).row,1).activate

[That's one line of code: I don't know how it renders on the messageboard.]

selection.paste [or pastespecial etc.]

application.cutcopymode=false