Set a range for and entire row then delete that row

Question

0.00/5 (No votes)

See more:

Since I have col's formatted, I can't insert a row; therefore, I need to delete a row. I try to set a range for the empty row, then delete that range so that I can insert a row. What I have is the following, but it doesn't work. To start with, the range is not being set to the row.

emptyRow is an int with the row number.

C#

 for(intRowIndex = RegGlobals.StartingRow; intRowIndex < emptyRow &&              !found; intRowIndex++)
{
   DateTime dtSheetDate = targetWorksheet.Cells[intRowIndex, RegGlobals.intDateCol].Value;
   string temp = dtSheetDate.ToString("MMM dd");
   if (DateTime.Parse(txtDate.Text) < dtSheetDate)
   {
      Excel.Range deleteRange = targetWorksheet.Rows[emptyRow];
      string temp1 = deleteRange.Text;
      deleteRange.Delete();
      targetWorksheet.Rows.Insert(intRowIndex);
      found = true;
   }
}

C#

public static int FindEmptyRow(int startingAt, int shNum, string coloumn)
       {
           int Ws = shNum;
           int cellRow = startingAt;
           string col = coloumn;

           bool found = false;

           Excel.Range activeCell = null;

           int emptyRow = startingAt;
           Excel.Range range;

           string workingRange = col + startingAt.ToString();
           try
           {
               // Get the selected worksheet
               Excel.Worksheet worksheet =
           (Excel.Worksheet)frmFoodPantry.theWorkbook.Worksheets.get_Item
           (shNum);
               while (found.Equals(false))
               {
                   // Keep going through all the rows to see if it is empty
                   workingRange = col + cellRow.ToString();
                   range = worksheet.get_Range(workingRange);

                   if (range.Value == null)
                   {
                       // It was empty so set the flag and the row found
                       found = true;
                       activeCell = worksheet.get_Range(workingRange);
                   }
                   else
                   {
                       // It was not found so increment to row to look at
                       cellRow++;
                   }
               }
           }
           catch
           {
               cellRow = startingAt;

           }
           // Return the empty row number
           return cellRow;
       }

What I have tried:

I have searched the net and tried several Microsoft help examples, but none of them is for an entire row.

Posted 21-Aug-23 2:02am

PaulaJoannAllen

Updated 29-Aug-23 3:42am

Deeksha Shenoy

v4

Add a Solution

3 solutions

Solution 1

You are using 'emptyRow' to define the range, but it's not clear how you are calculating the value for 'emptyRow'.

To find an empty row dynamically, you need to implement a method to identify an empty row index. You need to iterate through the rows in the worksheet and check for an empty row. My example method below called 'FindEmptyRow()' -

C#

int FindEmptyRow(Excel.Worksheet worksheet, int startRow)
{
    int lastRow = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;

    for (int rowIndex = startRow; rowIndex <= lastRow; rowIndex++)
    {
        bool isEmptyRow = true;

        for (int colIndex = 1; colIndex <= worksheet.Columns.Count; colIndex++)
        {
            Excel.Range cell = worksheet.Cells[rowIndex, colIndex];
            if (cell.Value != null)
            {
                isEmptyRow = false;
                break; //No need to check more columns...
            }
        }

        if (isEmptyRow)
        {
            return rowIndex;
        }
    }

    //If no empty row is found, return -1...
    return -1;
}

//Using your code above...

int emptyRow = FindEmptyRow(targetWorksheet, RegGlobals.StartingRow);

if (emptyRow != -1)
{
    int intRowIndex;
    bool found = false;

    for (intRowIndex = RegGlobals.StartingRow; intRowIndex < emptyRow && !found; intRowIndex++)
    {
        DateTime dtSheetDate = targetWorksheet.Cells[intRowIndex, RegGlobals.intDateCol].Value;
        if (DateTime.Parse(txtDate.Text) < dtSheetDate)
        {
            Excel.Range deleteRange = targetWorksheet.Rows[emptyRow];
            string temp1 = deleteRange.Text;
            deleteRange.Delete();
            targetWorksheet.Rows.Insert(intRowIndex);
            found = true;
        }
    }
}
else
{
    //Handle if no empty row was found...
}

Posted 21-Aug-23 4:08am

Andre Oosthuizen

Comments

PaulaJoannAllen 21-Aug-23 19:11pm

Sorry if my question was incomplete in its description. I have a routine for finding the empty row, which returns an int with the row number. I want to use that emptyRow value to create a range of the whole row and then delete the row so I can insert a row at the proper place.

Andre Oosthuizen 22-Aug-23 2:27am

Can you see why we always insist on as much information as possible. :) Please use "Improve Question" to update your question with the routine that you use as I have absolutely no idea what it is or do and based on that nobody can help you as we cannot see your screen or read your thoughts.

Solution 2

You can use the Range.EntireRow property (Excel) | Microsoft Learn[^] to delete a row.

Posted 21-Aug-23 22:24pm

Richard MacCutchan

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PaulaJoannAllen · Accepted Answer · 2023-08-23T14:11:00

To assign a range to a whole Excel row, use.

Excel.Range rngRow = frmFoodPantry.theWorkbook.ActiveSheet.Range[RegGlobals.dateCol + emptyRow.ToString()].EntireRow;

Excel.Range = Microsoft.Office.Interop.Excel.Range
rngRow = the range variable.
frmFoodPantry.theWorkbook.ActiveSheet = the worksheet.
RegGlobals.dateCol = a string representing the Col letter.
emptyRow.ToString() = a string representing the row number.