Excel Macro

  IntoPCs 15:31 18 Oct 2007
Locked

I am no good at VBA, so I need some help please. I recorded a short Macro that cuts a selected row from one worksheet and pastes it into another, then returns to the first sheet and deletes the empty row. It worked fine the first time, but after that it kept pasting the data into the same row and I lost the previous paste.
Looking at the VBA code the line that is causing the problem is:
Range("A420").Select
What can I replace ("A420") with to make it paste into the next blank row?

  silverous 15:50 18 Oct 2007

I would normally say:

range("A420").End(xlDown).Select

which will do the equivalent of pressing the end key then the down cursor key while on 420 however it only works if there is more than one entry in the list at 420 otherwise you get the bottom.

There may be smarter ways of doing it but how about

If range("A421") <> "" Then
range("A420").End(xlDown).Select
Else
range("A421").Select
End If

Which will go to the bottom of the column in A if there is something in A421 otherwise it will go to a421 ?

  IntoPCs 10:33 24 Oct 2007

Thanks for the input - it put me on the right track and the Macro now works nicely

  silverous 12:29 24 Oct 2007

Great. Thanks for letting us know, always nice to hear back.

  VoG II 16:49 24 Oct 2007

You shouldn't need to Select anything to work with it. e.g.

Sub test()
Dim NextRow As Long
NextRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Sheet1").Range("A1:F1").Cut Destination:=Sheets("Sheet2").Range("A" & NextRow)
Sheets("Sheet1").Rows(1).EntireRow.Delete
Application.CutCopyMode = False
End Sub

  silverous 18:44 24 Oct 2007

I know which one looks simpler to me, and it works!

  silverous 18:45 24 Oct 2007

To explain...The OP was obviously familiar with select but not too experienced at VBA so I thought best to keep simple and stick close to what they knew.

  Andy1991 19:17 24 Oct 2007

this will work for you, find the in the VBA script where it says

Range("[The cells to paste in]").select

after this, type the following in a new line and on words


Do While ActiveCell <> ""
ActiveCell.Offset(1, 0).Activate
Loop

  VoG II 19:26 24 Oct 2007

That might take a little while to run with potentially 65,656 (1,084,576 in Excel 2007) rows to loop through!

Better to just use an End(xlUp) expression to return the last filled row and add one to it. Selecting and Activating are unnecessary and time-consuming.

  Andy1991 19:31 24 Oct 2007

what if u record the macro but just after u paste it in the row, right click the row heading and click add, then continue the rest of the macro?

  Andy1991 19:32 24 Oct 2007

i'm only in college and i have been learning alot of Macro coding and editing personally, could you give me a definition on that code u just advised of, then i can learn more. yey!! lol

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?