Autofill down or Copy down 1 Row

[Kangah]

Hi All,

I have what I think should be an easy question but can't figure it out and Google isn't turning much up either...

I have a spreadsheet and I want a macro that will find the last row that contains data and simply copy it down one additional row...

If possible, it would be great if it could copy down the format and Data validation but not the actual contents (Otherwise I will just need to add a section that finds the last row, selects it and clears contents).

What would I do it find the last row with data and then copy down just one row?

Thanks in advance guys!

	A	B
1	Test 1	Data Validation (Dropdown Box)
2	Test 2	Data Validation (Dropdown Box)
3	Test 3	Data Validation (Dropdown Box)
4

<tbody>
</tbody>

 

[dermie_72]

This gives some tips on which code to use to find the last row:
Excel Find the last row of data in a range
as well as:
http://www.mrexcel.com/forum/excel-...g-last-row-via-visual-basic-applications.html

That will give you the last row, then just copy that, and paste to the lastrow + 1.

 

[Kangah]

Sorry, I was going to specify that I already know how to identify the last row but I din't want to limit the thinking to trying to get that particular type to work (especially if someone had a better way).

What I need to do is then select that entire row (From A & RowCount to J & RowCount) and Copy it down one row. The problem is that this changes as more rows are added each day.

What I was trying is;

Sub Add()


Dim RowCount As Integer
     RowCount = Worksheets("Orders").Cells(Rows.Count, "B").End(xlUp).Row
        
Dim RowPlus As Integer
     RowPlus = Worksheets("Orders").Cells(Rows.Count, "B").End(xlUp).Row + 1
    
Worksheets("Orders").Range("A" & RowCount & ":" & "J" & RowCount).AutoFill Destination:=Worksheets("Orders").Range("A" & RowPlus & ":" & "J" & RowPlus)


End Sub

 

[ukmikeb]

Kangah

I'd be very surprised if your autofill statement doesn't return an error.

Try :-

Worksheets("Orders").Range("A" & RowCount & ":" & "J" & RowCount).AutoFill Destination:=Worksheets("Orders").Range("A" & RowCount & ":" & "J" & RowCount).Resize(2)

You can then remove the definition of RowPlus and all references to it.

NB for an Autofill the destination must include the source.

hth

 

[Kangah]

Kangah

I'd be very surprised if your autofill statement doesn't return an error.

Try :-

Worksheets("Orders").Range("A" & RowCount & ":" & "J" & RowCount).AutoFill Destination:=Worksheets("Orders").Range("A" & RowCount & ":" & "J" & RowCount).Resize(2)

You can then remove the definition of RowPlus and all references to it.

NB for an Autofill the destination must include the source.

hth

Thanks very much for that! It works perfectly, and yes I was getting an error... Sort of knew what I wanted to do but the format/referencing just wasn't working for me. Awesome work!

 

[ukmikeb]

Kangah

Thanks for the feedback.

Glad you've got it working now.

Good luck with your project.