Macro to copy a certain a cell to a dynamic range

[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

 

[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

 

[Dave D-C]

[note improvement below]

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)

 

[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

[note improvement below]

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 =---

 

[Dave D-C]

.. 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

 

[Dave D-C]

[correction and addition]

.. 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