VBA And Finding The First Empty Row

Hi guys,
Looking for more help! This one should be quite simple though.

The system in use relies on the user copying and pasting a section from one tab to another. This is 8 columns wide, and 7 rows deep. In the past they've manually entered dates etc etc but I've used formulae to make this more efficient. The problem is, when they copy and paste now, the formulae update and are wrong. So I showed them the Paste Special Feature, which they loved, but then promptly forgot how to use. Anyway, I then decided to use a Macro attached to a button just to do it automatically and be done with it.

So, I've gotten this far:

Sub CopyandPasteNewQuarter()


Range("A9:M29").Select
Selection.Copy
Sheets("Stream1").Select
Range("A258").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

Now I did this just to check the commands would work (they do) and the selection of Cell A258 in stream 1 was done manually as I knew that that was the blank cell I needed. But obviously next quarter it won't be that cell, but A266.
So what commands would I need to introduce to instruct the macro to find the first blank row in a range, and then insert the block of cells to be copied in there?

Cheers in advance!!!

"The Cardinal" <[email protected]>
wrote in message
news:[email protected]...

Try using the .End(xldown/xlup/xlright/xlleft) command it is the program
equialtent of using ctrl & right/left/up/down arrow to navigate.
Worksheets("Main").Range("A1").End(xlDown) will return the last used cell in
column A assuming that the data is a complete column.
If you want the last used cell and the column may contain blanks then try
Worksheets("Main").Range("A65536").End(xlup) . This will start at the last
row and work upwards to the first occupied cell.

To find the first blank cell then just use the appropriate offset.

Andy

Nice! Thanks very much...

So how would I go to the first empty cell in a given row? The Code you gave would take me to the Last filled cell in a row.

So how would I go to the first empty cell in a given row? The Code you gave would take me to the Last filled cell in a row.

To find the first blank cell then just use the appropriate offset.

here's how to do so:
Set MyRange = MyWorkSheet.Range("A1").End(xlDown).Offset(1, 0)

Another to add to an old post
To add to the blanks below "G5" ... which has data ...
will progressively fill the blanks .. skipping other data if it is below and the fill gets to it

Set Ra = Range("g5").Columns(1)
Ra.End(xlDown)(2, 1) = "fbert"

Ra.End(xlDown)(2, 1) = "Herbt"
Ra.End(xlDown)(2, 1) = "HarryS tomoto"

' A better version to save errors doing first blank after data
'
' to progressively fill blanks in a column .. Past cells with values
'
Sub AddIntoRange(Ra As Range, PutV)
If Ra.Value = "" Then
Ra.Value = PutV
ElseIf Ra(2, 1) = "" Then Ra(2, 1) = PutV
Else
Ra.End(xlDown)(2, 1).Value = PutV
End If
End Sub
'use like
Private Sub CommandButton2_Click()
AddIntoRange Range("j3"), "cte"
AddIntoRange Range("j3"), 34
AddIntoRange Range("j3"), 45.67
AddIntoRange Range("j11"), 11
AddIntoRange Range("j11"), "freds good"
AddIntoRange Range("j11"), "freds cool"
AddIntoRange Range("j11"), "freds handy good"
AddIntoRange Range("j22"), "I think its OK now maybe "
AddIntoRange Range("j22"), "seems ok"


End Sub