CODEWITHSUNDEEP
vbaexcel
Luke Hsu
Luke Hsu

Reputation: 179

Pasting formula from last row to end of reference row

I am using excel for a certain processing work. I have about 250,000 rows of data spanning a few columns so processing will take a few minutes. New rows of data are always added, hence, to run the macro the last rows of the column B will be taken. This is so that the formulas below can be pasted for an x-number of rows.

Sub macro2()


Dim lastrow As Long


lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Range("J6:J" & lastrow).Formula = "GCDnm(D6,E6)"
Range("K6:K" & lastrow).Formula = "LOOKUP(....)"

End Sub

Is there a way to make this code more efficient? For example, instead of pasting the formula from J6 or K6 to last row (J250000 or K250000) everytime you run the macro? Is there way where by it pastes the formula from J230000 to J250000 instead?

Upvotes: 1

Views: 1658

Answers (2)

Milanor
Milanor

Reputation: 257

Due to the limitations of the comment section, i need to post it to the answer section.

Okay, firstly i thought you want the formula to be starting from row 6.

If you use .Formula function, the formula inside the cells will be static (same as what written inside the string past equal mark).

There are 2 ways to do it:

First, you can use looping from lastrowJ until lastrow like this:

For i = lastrowJ + 1 to lastrow
    Range("J" & i).Formula = "GCDnm(D" & i & ",E" & i)"
Next

do the same for K column.

The second method is by using .Paste method in which will behave like doing copy and paste manually on excel (the rows will automatically increased to follow the respected row) Example:

Range("J1").Select
    Selection.Copy
Range("J" & lastrowJ+1 & ":J" & lastrow).Select
    ActiveSheet.Paste

However, the latter method didn't reflect good programming practices and in which i prefer using earlier mentioned method over the latter.

Upvotes: 0

Milanor
Milanor

Reputation: 257

Yes you can, you just need to detect the lastrow of column J & K

Try out something like this:

Sub macro2()


Dim lastrow, lastrowJ, lastrowK As Long


lastrow = Cells(Rows.Count, "B").End(xlUp).Row
lastrowJ = Cells(Rows.Count, "J").End(xlUp).Row
lastrowK = Cells(Rows.Count, "K").End(xlUp).Row

Range("J" & lastrowJ + 1 & ":J" & lastrow).Formula = "GCDnm(D6,E6)"
Range("K" & lastrowK + 1 & ":K" & lastrow).Formula = "LOOKUP(....)"

End Sub

Hope it works :)

Upvotes: 1

Related Questions