• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

MS Excel VB copying a formula in to an undertermined amount of rows

Having recorded an extensive VB macro which sorts, adds/deletes, modifies, and recalculates a fairly large ASCII file, I run in to a problem when additional records are added to this file.  Initially, part of the macro was set to copy a formula in cell D1 and paste it to an inserted column C1:C1129.  Now that additional records have been added, the macro still only pastes to the extent of C1129.  How can I have the macro copy the information in cell D1 and paste it to an (un)-predetermined amount of rows in column C to compensate for any additional records added?  
  • 2
1 Solution

The problem maybe is  that you have defined a fixed number of rows or column in your code.

Try to insert an algorithm that will first determine the last row or column of the data and let it be the maximum number of row or column especially in your loops.

I hope this will help you. Just tell if you need a sample of this code, I'll be very glad to send it to you.


The code you want is....

Sub Copying()
    Range("D1", Range("C65536").End(xlUp).Offset(0, 1)).Select
End Sub

This will select D1:D??? based on column C being filled....i.e. if column C is filled to row 13 then Range D1:D13 will be selected.

Hope this helps

This also assumes you are using excel 97, if you are not you will need to change C65536 to C16384...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now