# Microsoft Office Application Help - Excel Help forum > Excel General >  >  VBA And Finding The First Empty Row

## The Cardinal

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!!!

----------


## Andy

"The Cardinal" <The.Cardinal.2bnfod_1154079306.2555@excelforum-nospam.com>
wrote in message
news:The.Cardinal.2bnfod_1154079306.2555@excelforum-nospam.com...

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

----------


## The Cardinal

Nice! Thanks very much...

----------


## mazen425

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.

----------


## wRatte

> 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)

----------


## shg

Welcome to the forum.

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.

----------


## harrys

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"

----------


## harrys

' 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

----------

