Macro to copy a certain a cell to a dynamic range

F

Frank Situmorang

Hello,

I have a cell containing formula, that I want to copy to more than 50,000
lines, instead using page down maybe some could tell me a macro on this.

This is illustration:
AI4
A..............................AI
7 7
..
..
..
...
50.000 50,000

I need to copy AI4 to AI7 ( always start to AI 50,000 ( dynamic)

Formula count cant be used from A7 to dynamically A onward..

There is no data on column AI. I want to use this formula to be an
identifier rows that I want to delete.

I appreciate any ideas provided.

Thanks in advance
 
D

Dave D-C

.. maybe some could tell me a macro on this.
My non-english isn't very good, but I hope this helps:
(using 50 instead of 50000 and A instead of AI)
Sub Sub1()
Const LastRow = 50, NumRows = 44
'-- the non-dynamic verswion:
Range("A4").Copy Range("a7:a50")
'-- dynamic if you have the last row:
Range("A4").Copy Range(Cells(7, 2), Cells(LastRow, 2))
'-- dynamic if you have the number of rows:
Range("A4").Copy Range(Cells(7, 3), Cells(7 + NumRows - 1, 3))
End Sub ' Dave D-C
 
D

Dave D-C

[note improvement below]
Dave D-C said:
My non-english isn't very good, but I hope this helps:
(using 50 instead of 50000 and A instead of AI)
Sub Sub1()
Const LastRow = 50, NumRows = 44
'-- the non-dynamic verswion:
Range("A4").Copy Range("a7:a50")
'-- dynamic if you have the last row:
Range("A4").Copy Range(Cells(7, 2), Cells(LastRow, 2))
'-- dynamic if you have the number of rows:
Range("A4").Copy Range(Cells(7, 3), Cells(7 + NumRows - 1, 3))
'-- this is better for the last case:
Range("A4").Copy Range("D7").Resize(NumRows, 1)
 
F

Frank Situmorang

Dave:

Maybe because we are both non-English speakers, you misunderstood my postings.

The range downword is dynamics, means could increase day by day, so I think
you can not constant last row to 50.

Also you can not change AI to A, because I want to copy the formula exactly
in Column AI while there is no data in this column below the formula that I
want to copy. There is a data in column A so I guess we can use the function
count, but I do not know how to do all this, because I am not an IT people. I
am an accountant in fact, but I want to know IT as a hobby.

Thanks in advance for your help.

Hello, can anyone help us
--
H. Frank Situmorang


Dave D-C said:
[note improvement below]
Dave D-C said:
My non-english isn't very good, but I hope this helps:
(using 50 instead of 50000 and A instead of AI)
Sub Sub1()
Const LastRow = 50, NumRows = 44
'-- the non-dynamic verswion:
Range("A4").Copy Range("a7:a50")
'-- dynamic if you have the last row:
Range("A4").Copy Range(Cells(7, 2), Cells(LastRow, 2))
'-- dynamic if you have the number of rows:
Range("A4").Copy Range(Cells(7, 3), Cells(7 + NumRows - 1, 3))
'-- this is better for the last case:
Range("A4").Copy Range("D7").Resize(NumRows, 1)
End Sub ' Dave D-C
----== Posted via Pronews.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.pronews.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= - Total Privacy via Encryption =---
 
D

Dave D-C

Frank Situmorang said:
.. you can not constant last row to 50.
.. Also you can not change AI to A,

OK, is this better?
Sub Sub1()
dim iLastRow&, iColumn&
iColumn = Range("AI1").Column
iLastRow = [you put something here]
'-- dynamic if you have the last row:
Range("AI4").Copy Range(Cells("AI7"), Cells(iLastRow, iColumn))
End Sub ' Dave D-C
 
D

Dave D-C

[correction and addition]

Frank Situmorang said:
.. you can not constant last row to 50.
.. Also you can not change AI to A,

OK, is this better?
Sub Sub1()
Dim iLastRow&, iColumn&
iColumn = Range("AI1").Column
iLastRow = [you put something here]
'-- dynamic if you have the last row:
Range("AI4").Copy Range("AI7", Cells(iLastRow, iColumn))
End Sub ' Dave

This is equivalent:
Sub Sub2()
Dim iLastRow&
iLastRow = [you put something here]
'-- dynamic if you have the last row:
Range("AI4").Copy Range("AI7").Resize(iLastRow - 7 + 1, 1)
End Sub ' Dave
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top