Master of Management Systems (Micros.Dynamic)

[Vincenzo]

Hey, I need a solution with a macro, I'm just learning this excellent function excel, but I need your help. I have a file that has two excel spreadsheets, one is called a debt and the other is called pay, I use it for work.

What I want is that the macro, cut the line that I select in debts, and paste in the first empty row of the spreadsheet and then paid is situated in the next row so that when you select a row of debts the copy in the next row of empty pay, after that I want to go back and remove that row debts that remained empty after the court.

They command the macro I created for this, the only problem is that I always copy in the same row of the sheet debts

'Shortcut: CTRL + a 
' 
Selection.Cut 
Sheets ( "PAID"). Select 
Rows ( "3:3"). Select 
ActiveSheet.Paste 
Range ( "A4"). Select 
Sheets ( "DEBT"). Select 
Selection.Delete Shift: = xlUp 
Range ( "A13"). Select 
End Sub

[Katty]

The issue is that before pasting the data, you must "see" what is the last row occupied, add to that a row, store that value to a variable and use it to define the cell where it is copied. I tell you as it does, then adapt it to your macro:
VerUltimaFile sub ()
UlimaFile dim as Long

'With this instruction "upload" since the end of the road until the last row occupied:
UltimaFile = Sheets ( "Paid"). Cells (65536.1). End (xlUp). Row
'you add one, so that data stick below:
UltimaFile = UltimaFile + 1
'and paste the copied data before:
Sheets ( "Paid"). Cells (UltimaFila, 1). PasteSpecial xlPasteAll
End Sub

Fix that in the last instruction to use as UltimaFile of the row where they must copy the data.
Practical that if a little goes well, tell me anything.

[Zecho]

Another way to find the last cell is empty this:

Sub ultimaline () 

Range ( "a1"). Activate 
Do While Not IsEmpty (ActiveCell) 
ActiveCell.Offset (1, 0). Activate 
Loop 

End Sub

[Katty]

Accurate, so use the basic control structures programming.
have only one problem:
whether we are talking about a few rows, the time spent by each procedure is virtually the same. However if the table in question (for example) 56,000 rows, execution times are changing as many times, because with the loop, vba must iterate to find the 56,000 empty... 9000 or from the bottom up, until you find the first busy. And See, that for each iteration should also assess a condition, thus execution times are doubled.

Row = Cells (65536, 1). End (xlUp). Row

I say to excel: 65,536 from the row (of column 1), desolve the number of the first row occupied and takes 0.000000000000001 seconds to do so, regardless of the number of records that exist.
VBA provides us with many tools and features that allow us to put aside the classical structures and provide our applications faster.
something else: the fact use the "activate" and "select" much too slow implementation of the macro. we just call them if we need to choose whether or activate the cell.

let's take an example:
I delete a row:

Range ( "a1"). Select 
Selection.EntireRow.Delete

I also delete the row, but using less code:

Range ( "A1"). EntiereRow.Delete

Does it mean the idea?
is a small input and clarification, go to the applications that perform better.

a trick:
Many times when you run a macro that clears leaves or rows, columns, etc, etc, we can see the screen as "blink" ... this is "fatal", speaking in terms of time, because the PC should send data to the monitor, and so on.
this "flicker" can be avoided with an instruction:

Example:

Sub BorrarFiles () 
Dim X as Long 
'I am here to refresh the screen is terminated: 
Application.ScreenUpdating = False 
'beginning with the erasure: 
For X = 10 to 100 
Cells (X, 1). EntireRow.Delete 
Next X 
'and always turn back to what disabled: 
Application.ScreenUpdating = True 
End Sub