Check to see if cell is blank

M

Munchkin

I want my macro to first check & see if A9 is blank. If it is I want to
paste copied data in A9, if it is not blank I want to proceed with finding
the 1st blank cell in column A and pasting there (which is how the macro is
set up)


Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1).Activate

ActiveSheet.Paste
Columns("D:D").EntireColumn.AutoFit
 
G

GTVT06

Try:

Sub Macro1()
Dim i As Integer
i = Range("A65536").End(xlUp).Row + 1

If Range("A9") = "" Then
Range("A9").PasteSpecial xlPasteAll
Else
i = Range("A65536").End(xlUp).Row + 1
Cells(i, 1).PasteSpecial xlPasteAll
End If

End Sub
 
G

GTVT06

Sorry, I assigned a value to i twice, you can remove one:

Sub Macro1()
Dim i As Integer

If Range("A9") = "" Then
    Range("A9").PasteSpecial xlPasteAll
        Else
        i = Range("A65536").End(xlUp).Row + 1
        Cells(i, 1).PasteSpecial xlPasteAll
End If

End Sub
 
P

Peter T

One way,

Sub test()
Dim rCopy As Range, rDest As Range

Set rCopy = ActiveSheet.Range("A1:D1")
Set rDest = ActiveSheet.Range("A24")

If Len(rDest) Then
If Len(rDest.Offset(1, 0)) Then
Set rDest = rDest.End(xlDown).Offset(1, 0)
Else
Set rDest = rDest.Offset(1, 0)
End If
End If

rCopy.Copy rDest

End Sub

Note no need to select

Regards,
Peter T
 
P

Peter T

typo, change

Set rDest = ActiveSheet.Range("A24")
to
Set rDest = ActiveSheet.Range("A9")

Peter T
 
D

Don Guillett

1. Post ALL of your code for comments
2. Is there data in col a below where you think the next blank cell should
be or will there just be blanks below.

sub copytonar()'copies to last available row in col A
lr=cells(rows.count,1).end(xlup).row +1
if len(application.trim(range("a9")))<1 then lr =9
range("c2:c22").copy cells(lr,1)
end sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top