Insert Row with Offset

[cha]

Hi,
How do you insert a new row offset one column to the right?

ActiveCell.Offset(1,1).EntireRow.Insert shift:=xlDown

doesn't work.
</pre>

 

[wigi]

I don't quite get it. What is the layout of the data, before and after the row is inserted?

 

[Rick Rothstein]

If I understand your question correctly, you want to insert a row without affecting Column A. See if this line of code does what you want...

Cells(ActiveCell.Row, 2).Resize(, Columns.Count - 1).Insert Shift:=xlDown

 

[cha]

Hm, that line isn't working for me. I'm getting a sizing error.
I'm trying to insert a new row below the activecell, then pasting the data 1 column to the right.

 

[Rick Rothstein]

Hm, that line isn't working for me. I'm getting a sizing error.

The code was tested before I posted it and no sizing errors occurred. If you retyped it (instead of simply copy/pasting it), then maybe you missed the comma? No matter, I am pretty sure this code is not doing what you actually want based on the additional description you just provided.

Hm, that line isn't working for me. I'm getting a sizing error.
I'm trying to insert a new row below the activecell, then pasting the data 1 column to the right.

Your descriptions (the original and this current one) are not entirely clear to me. Can you show us a short (maybe 3 rows and 4 columns) before AND after example of your data so we get a clearer picture of your intent?

 

[cha]

Each class is already mapped to a family. (A>f1, B>f2, G>f1)

The original data table from Sheet1:
<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width:48pt" span="3" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">Class</td> <td style="width:48pt" width="64">Date</td> <td style="width:48pt" width="64">Duration</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">A</td> <td class="xl65" align="right">1/1/2011</td> <td>2 days</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">G</td> <td class="xl65" align="right">1/3/2011</td> <td>1 day</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">B</td> <td class="xl65" align="right">1/3/2011</td> <td>3 days</td> </tr> </tbody></table>
Sheet2 before:
<table border="0" cellpadding="0" cellspacing="0" width="227"><col style="mso-width-source:userset;mso-width-alt:1755;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:1828;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:36pt" height="20" width="48">Family</td> <td style="width:38pt" width="50">Class</td> <td class="xl65" style="width:51pt" width="68">Date</td> <td style="width:46pt" width="61">Duration</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">f1</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">f2</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">f3</td> <td>
</td> <td class="xl65" style="width:51pt" width="68">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">f4</td> <td>
</td> <td class="xl65" style="width:51pt" width="68">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">f5</td> <td>
</td> <td class="xl65" style="width:51pt" width="68">
</td> <td>
</td> </tr> </tbody></table>


Sheet2 after:
<table border="0" cellpadding="0" cellspacing="0" width="227"><col style="mso-width-source:userset;mso-width-alt:1755;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:1828;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:36pt" height="20" width="48">Family</td> <td style="width:38pt" width="50">Class</td> <td style="width:51pt" width="68">Date</td> <td style="width:46pt" width="61">Duration</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">f1</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>A</td> <td class="xl66" align="right">1/1/2011</td> <td>2 days</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>G</td> <td class="xl66" align="right">1/3/2011</td> <td>1 day</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">f2</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>B</td> <td class="xl66" align="right">1/3/2011</td> <td>3 days</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">f3</td> <td>
</td> <td class="xl65" style="width:51pt" width="68">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">f4</td> <td>
</td> <td class="xl65" style="width:51pt" width="68">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">f5</td> <td>
</td> <td class="xl65" style="width:51pt" width="68">
</td> <td>
</td> </tr> </tbody></table>

 

[Rick Rothstein]

Each class is already mapped to a family. (A>f1, B>f2, G>f1)

The charts you posted are fine with one exception... where is the above information located on the worksheet? In other words, how do we indentify which family goes with which letter?

 

[cha]

I used vlookup to access the Family values from a different workbook. I stored it in a variable f as variant.

 

[Rick Rothstein]

I used vlookup to access the Family values from a different workbook. I stored it in a variable f as variant.

In order to do what you asked for, we will need access to all the mappings at one time. Do you have a column for that in your original data? If not, do you have an array containing the mapping?

 

[cha]

Nope, I don't, but I can make a column for them on Sheet1