Geek Answers Handbook

Excel Automation: how to set a range?

The standard method to get the range of Excel spreadsheet used is the UsedRange method. I can copy all used cells as follows:

xlWorkSheet.UsedRange.Copy(misValue);

Unfortunately, this is not a good method, because the cells are "activated" if the user writes something in one, and then deletes it again. This may have an unintended consequence that thousands of empty rows and columns are marked (and printed in my case).

So, I translated this method in C # to get a more accurate range. When debugging, it gives FirstRow = 1, FirstColumn = 1, LastRow = 600, LastColumn = 2, which I would like to use a test sheet as input.

But I still need to set the actual used range and copy it. In VB, this is done as follows:

Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn))

How to set a range and copy it to C #? I want to replace this line:

xlWorkSheet.UsedRange.Copy(misValue);

In the range from (1.1) to (600.2).

I tried this:

return Excel.Range(xlWorkSheet.Cells[FirstRow, FirstColumn], xlWorkSheet.Cells[LastRow, LastColumn]);

But it gives Excel.Range - this is a "type" that is not valid in this context. I do not know how to write it correctly.

    // this struct is just to make the result more readable
    public struct RealRange
    {
        public int FirstRow;
        public int FirstColumn;
        public int LastRow;
        public int LastColumn;

        public RealRange(int fr, int fc, int lr, int lc)
        {
            FirstRow = fr;
            FirstColumn = fc;
            LastRow = lr;
            LastColumn = lc;
        }
    }

    public RealRange RealUsedRange()
    {
        int FirstRow = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByRows,
            Excel.XlSearchDirection.xlNext,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Row;

        int FirstColumn = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByColumns,
            Excel.XlSearchDirection.xlNext,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Column;

        int LastRow = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByRows,
            Excel.XlSearchDirection.xlPrevious,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Row;

        int LastColumn = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByColumns,
            Excel.XlSearchDirection.xlPrevious,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Column;

       return new RealRange(FirstRow, FirstColumn, LastRow, LastColumn);
    }

Solution Notice that I changed the return value from void to a RealRange structure. This should make the result more readable. A "range" in a spreadsheet is the gap between two cells. You can copy the range using the get_range function, as shown below. Thanks to D. Hilgart for your help.

rr = RealUsedRange();
this.workSheet.get_Range(
   this.workSheet.Cells[rr.FirstRow, rr.FirstColumn], 
   this.workSheet.Cells[rr.LastRow, rr.LastColumn]).Copy(missing);
+5
source share
1 answer

, , RealUsedRange Range, .

- :

public Range RealUsedRange()
{
    // ...
    return xlWorkSheet.get_Range(xlWorkSheet.Cells[FirstRow, FirstColumn],
                                 xlWorkSheet.Cells[LastRow, LastColumn]);
}
+2

More articles:


All Articles