Insert Row & Insert Column Macros

I have a "Task Tracker" workbook that uses columns A:J to display and calculate task information (A:E are for user-entered data, F:J contain formulas that use the information in C:E and perform calculations. F:J are hidden cells in the user view.) The remainder of the columns display a heat map of where the tasks fall on a timeline, and whether or not they are running on time, are behind, or are complete.

There are two buttons for users to use: one to insert a new row, and one to insert a new column. The InsertRow() macro inserts a row into the list, then copies the formulas down from the above row. The InsertColumn() macro locates the last used column in the worksheet and copies everything over from the column to the left of it.

Originally, I had a macro for InsertRow using Range that copied from Column F (where the formulas start) to Column XFD. However, once I created the InsertColumn macro I realized that I cannot do InsertRow like that because InsertColumn needs to locate the last data-containing column in the worksheet and add a new one to the right...and if InsertRow gets run first, InsertColumn won't work because the value for lastColumn comes back as the index of column XFD.

What I am looking for help with: I need to locate the lastColumn value in my InsertRow macro, then use that value as part of the Range when the program executes the Copy/Paste portion of the code. I think that the problem I'm having has to do with the fact that the code I'm using to find the last column returns the index, and the Range function needs the name of the column.

Here is what I have for both macros:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

(Image of worksheet is attached.)

Picture1.jpg

Please Login or Register  to view this content.

Hi, amfrancis,

the benefit of declaring variables at the top of a procedure coems when you add Option Explicit to the very top of that procedure.

You declared

Please Login or Register  to view this content.

and later used

Please Login or Register  to view this content.

Please Login or Register  to view this content.

You could copy the values directly by using

Please Login or Register  to view this content.

Ciao,
Holger

Hi, Angela,

when using copy you copy all information from the cells: formulas, font, size, colour, indention, where the text is located - if you use Value you will only get the values from the cells, nothing else. It really depends on what you have in the cells you want to copy and how the result should be treated. Copy is what you do when pasting while Value is equal to PasteSepcial xlPasteValues.

Regarding the "error": as you used the same name for the variable you got the result you expected, it would have been irritating if you used the correct variable for getting the last row number but the one with 2rr for trying to copy (variable would be 0 and raise an error).

HTH,
Holger

Hi Holger,

Thank you for the clarification. Copy is what I need to use, since I'm only copying formulas over (not values). I then use conditional formatting to determine how the cell should be colored, based on the results of the formula (but only after the user enters data in columns D & E).

I'm having a new problem now, though, that maybe you can help me with...

My InsertColumn macro was working perfectly, until I made the changes to InsertRow. Now the process of finding the last used column isn't working correctly. Rather than finding the last column in the worksheet that contains data, it's finding the last column used at any point.

Example: The last column index was 35. I used the Insert Column button to insert 5 columns. I then deleted these 5 columns. Now, the last column index is being returned as 41, rather than 35. Do you know why this would be happening?

Please Login or Register  to view this content.

Hi, Angela,

please do yourself and us a favour and add code-tags whenever you post procedures here on Excel-Forum as only that would allow us to answer to your thread. Arlette was kind enough to add tjem for you in the opening post.

If you need information on how to do so please have a look at Rule #3.

Ciao,
Holger

Note: delete post if needed after OP complied with the rules.

Hi, Angela,

what you discovered is a known issue with a command like

Please Login or Register  to view this content.

The range will expand but refuse to shrink unless you reset the range to the last used row and column, save the workbook (which resets the cell to show the correct column) or slightly alter the code and use

Please Login or Register  to view this content.

BTW: Option Explicit didn´t like lastColumn in your sample.

Ciao,
Holger