How to insert a new row in the specified cell only of Excel sheet using c

0 votes

So I have this code:

        using Excel = Microsoft.Office.Interop.Excel;
        ...

        public static void AppendColumnValuesGivenStart(Excel.Worksheet wks, int column, int columnStarts, int totalColumnsInTable, List<ColumnValues> columnValues)
        {
            for (int x = 0; x < columnValues.Count; x++)
            {
                for (int y = 0; y < totalColumnsInTable; y++)
                {
                    wks.Cells[x + columnStarts, column + y].Insert(Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
                }
            }
        }

It is supposed to insert a new row at the specified position of the cell, but in the output, it makes a new row that spans all the way to the right and interferes with other tables.

What am I doing wrong and what would be the correct way of inserting a new row in the specified cell?

Nov 24 in Others by Kithuzzz
• 20,660 points
23 views

1 answer to this question.

0 votes

I have this worksheet with a matrix of data ...

Before

I then want to insert 5 rows between columns E and J to give me a result that looks like this ...

After

To achieve this, there are a few ways to do it.

You can do it by selecting the range specifically and running the insert method ...

xlWorksheet.Range["E6:J10"].Insert(XlInsertShiftDirection.xlShiftDown);

You can obviously pass in parameters to make it happen as well ...

int rowFrom = 6;
int rowTo = 10;

xlWorksheet.Range[$"E{rowFrom}:J{rowTo}"].Insert(XlInsertShiftDirection.xlShiftDown);

Or you can do it using cell references ...

var cellAddressFrom = ((Range)xlWorksheet.Cells[6, 5]).Address;
var cellAddressTo = ((Range)xlWorksheet.Cells[10, 10]).Address;

xlWorksheet.Range[$"{cellAddressFrom}:{cellAddressTo}"].Insert(XlInsertShiftDirection.xlShiftDown);

Or more again, cell references using column letters, not numbers ...

var cellAddressFrom = ((Range)xlWorksheet.Cells[6, "E"]).Address;
var cellAddressTo = ((Range)xlWorksheet.Cells[10, "J"]).Address;

xlWorksheet.Range[$"{cellAddressFrom}:{cellAddressTo}"].Insert(XlInsertShiftDirection.xlShiftDown);

The bottom line, you need to get your parameters right and you need to make up a matrix (unless it's a single cell you want to shift down) in order to shift cells down or right.

answered Nov 24 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to insert a picture into Excel at a specified cell position with VBA

Try this: With xlApp.ActiveSheet.Pictures.Insert(PicPath) With ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
150 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
62 views
0 votes
1 answer

Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

answered Oct 27 in Others by narikkadan
• 37,660 points
73 views
0 votes
1 answer

Generate a flat list of all excel cell formulas

Hello, you'll have to follow certain steps ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
79 views
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23 in Database by gaurav
• 22,040 points
143 views
0 votes
1 answer

Deleting duplicate rows in Excel using Epplus

You need to re-think this… the while ...READ MORE

answered Feb 23 in Database by gaurav
• 22,040 points
296 views
0 votes
1 answer

Export DataTable to Excel File

Add Interop References. First we need to ...READ MORE

answered Jun 9 in JQuery by gaurav
• 22,040 points
109 views
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18 in Others by narikkadan
• 37,660 points
33 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP