CODEWITHSUNDEEP
excel
Mike Mc
Mike Mc

Reputation: 3

Excel: how to paste to first blank cell?

I'm trying to paste info to the first blank cell in colum A of a sheet? How can I do this?

This is what I have done but it paste the info 1000 times. What do I need to change?

Thanks in advance.

Range("B2:E2").Select                                        'Selet info to copy
Selection.Copy                                               'Copy
Sheets(Range("A2").Value).Select                             'Goto Sheet Listed in cell A2

Dim i                                                        'define i
    For i = 3 To 1000                                        'Set up loop size
        If Range("A" & CStr(i)).Value = "" Then              'If Cell Ai is blank
            Range("A" & i).Select
            ActiveSheet.Paste                                'Paste info
        End If
    Next i
    End If

Upvotes: 0

Views: 8030

Answers (2)

Floris
Floris

Reputation: 46375

While modifying the loop with an Exit For will work, there is a much better approach - finding the last cell in a column can be achieved with

Set lastCell = Range("A1").End(xlDown)
set freeCell = lastCell.Offset(1,0)

This assumes that there is at least one cell below A1. If you know (as in your example) that there will never be more than 1000 rows occupied, you can do this in the other direction:

Function freeCell(r As Range) As Range
' returns first free cell below r
  Dim lc As Range     ' last used cell on sheet
  Set lc = r.Offset(1000, 0).End(xlUp).Offset(1, 0)
  Set freeCell = lc
End Function

Sub testIt()
  Dim p As Range
  Set p = freeCell(Range("A3"))
  MsgBox "the address of p is " & p.Address
End Sub

The function freeCell returns the cell you are after; the sub testIt shows that it works (and how it is called). In your case, you can greatly simplify your code to

Sub doIt()
  Dim sh As Worksheet, tCell As Range
  Sheets("Sheet1").Range("B2:E2").Copy
  Set sh = Sheets(Range("A2").Value)
  Set tCell = freeCell(sh.Range("A3"))
  sh.Paste tCell
End Sub

Note - when you record a macro, you get lots of Activate, Select etc commands sneaking in. These can usually be avoided - and there are plenty of excellent articles online (and on this site) explaining why you would want to avoid them. The above snipped shows how to copy from one sheet to another without any of these.

If you are never sure that there is anything on your target sheet (no header row in row 2, for example) you could modify your code so the target cell is never above row 3:

If tCell.Row < 3 Then Set tCell = tCell.Offset(3 - tCell.Row)

Upvotes: 1

CustomX
CustomX

Reputation: 10113

Your FOR LOOP will run from cell A3 until A1000 and for every empty cell it will paste the value. You want to exit your loop as soon as the condition is matched. You want to add an Exit For condition.

If Range("A" & CStr(i)).Value = "" Then
    Range("A" & i).Select
    ActiveSheet.Paste
    Exit For
End If

Source

Upvotes: 0

Related Questions