Insert New Row in a block of data

[Clare]

This is the code I have, and i want this to either be added in a new row (as there is other information below this line) or add a new row after the form adds the information to the spreadsheet?


Private Sub CommandButton1_Click()

ActiveWorkbook.Sheets("Jobs").Activate

Range("B7").Select

Do

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = Job_No
Range("B7").Select

End Sub

[jetted]

Hi Claire

Not to sure what you want

but the code to insert a row would be:

Selection.EntireRow.Insert

[Dave Peterson]

Maybe...

Option Explicit
Private Sub CommandButton1_Click()
dim DestCell as Range
with activeworkbook.sheets("Jobs")
set destcell = .cells(.rows.count,"B").end(xlup)
if destcell.row < 7 then
set destcell = .range("B7")
end if
if isempty(destcell.value) then
'it's already empty
'so do nothing
else
'come down one row
set destcell = destcell.offset(1,0)
end if

destcell.value = Job_No.Text 'from a textbox???

end if



Clare wrote:
>
> This is the code I have, and i want this to either be added in a new row
> (as there is other information below this line) or add a new row after
> the form adds the information to the spreadsheet?
>
> Private Sub CommandButton1_Click()
>
> ActiveWorkbook.Sheets("Jobs").Activate
>
> Range("B7").Select
>
> Do
>
> If IsEmpty(ActiveCell) = False Then
>
> ActiveCell.Offset(1, 0).Select
>
> End If
>
> Loop Until IsEmpty(ActiveCell) = True
>
> ActiveCell.Value = Job_No
> Range("B7").Select
>
> End Sub
>
> --
> Clare
> ------------------------------------------------------------------------
> Clare's Profile: http://www.excelforum.com/member.php...o&userid=36047
> View this thread: http://www.excelforum.com/showthread...hreadid=558463

--

Dave Peterson