Macro to select next empty cell

[MrGJEB]

Hello, apologies for this query as i am sure there is a thread somewhere that answers it...but i cant seem to find one.

I am putting together a fairly simple spreasheet for collating survey returns. I am writing a macro that automatically copies the answers from the survey sheet into my spreadsheet.

I seem to be having trouble with the simplest part (in red below).

In row 1 have the column headings, i need a line of code that automatically selects the next empty row (or cell in column A) (e.g. for the first entry it should select A2, then A3 and so on) so that i can copy in the new rows.

As you can see from the code below the new row is currently copied into the bottom of the spreadsheet.

The code is currently:

Sub GIWfeedback()
' This code automatically collates the feedback returns
' Firstly the word "Collated" is entered onto the feedback return to
' ensure the user does not collate the same information twice
Range("F11").Value = "COLLATED"

' Secondly first row of information is copied
Rows("1:1").EntireRow.Select
Selection.Copy

' This is then pasted into an empty line on the "Survey Returns" sheet in the GIW Survey returns
' spreadsheet. The name of the spreadsheet must not be changed.

Windows("GIW Survey Returns.xls").Activate
Sheets("Survey Returns").Select
Range("$A$1").Select
If ActiveCell.Value = "" Then

ActiveCell.Select
Else

Selection.End(xlUp).Select
ActiveCell.Select
End If

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

' Thirdly the row containing the comments is copied
Windows("GIW Internal Feedback Form.xls").Activate
Sheets("Sheet1").Select
Rows("4:4").EntireRow.Select
Selection.Copy

' This is then pasted into an empty line on the "Additional Comment" sheet in the GIW
' Survey Returns spreadsheet. The name of the spreadsheet must not be changed.

Windows("GIW Survey Returns.xls").Activate
Sheets("Additional Comments").Select
Range("$A$1").Select
If ActiveCell.Value = "" Then

ActiveCell.Select
Else

Selection.End(xlUp).Select
ActiveCell.Select
End If

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Thank you for your help.

Grant

 

[VoG]

Instead of your code in red try

Range("A" & Rows.Count).End(xlUp).Offset(1).Select

 

[MrGJEB]

It works fine. Thank you very much