none
Set Excel Sheet1 all rows height to specific value without using range, at runtime.

    Question

  • Hi,

    I am creating Sheet dynamically at runtime with the click event of button.

    Excel.Worksheet newSheet = (Excel.Worksheet) Globals.ThisWorkbook.Worksheets.Add();

    newSheet.Name = "MySheet";

    newSheet.Range[1,1000].RowHeight = 12; // NEED TO SET ALL ROWS HEIGHT TO 12 FOR THIS SHEET, right now hardcoded row range 1 to 1000 .... Correct????

    I have added some styles and some information to cells.

    This works fine. Till row 10 I have the mandatory information needed when I create sheet. i have some controls in ribbon to do some user action.

    Now I want to find the Row from where I can start writing information to cells, like in first case it would be row 11. But when user has already done performed some action and data is written from row 11 to row 20 (assume in first case I hardcoded row 11).

    Now how do I find the row 21 to start writing when user action is done.

    I have used int countRows = newSheet.UsedRange.EntireRow.Count; but this gives 1000 as I had edited row height till 1000.

    Any idea on how to get the row from where to start writing. May be getting last row where some thing is written in some cell. Any help would be appreciated as I am new to VSTO development.

    Thanks,

    Harvinder

    Tuesday, July 30, 2013 11:45 AM

Answers

  • Hi Harvinder

    Although you're using a VSTO Workbook, the question your have is more about how the Excel object model works. VSTO builds on the Excel object model and, for the most part, that's what your code works with. In this case, it is definitely the Excel object model. The people who know best how to work with the Excel object model congregate in the Excel for Developers forum. You might have better luck asking the question "How do I locate the first empty cell" there, than here...

    Would you like me to move the question for you?


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, August 01, 2013 2:40 PM

All replies

  • Hi Harvinder,

    I will consult my colleagues on this question. And it will take some time, your patience will be greatly appreciated.

    Regards,

    Jeffrey 


    Jeffrey Chen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 31, 2013 12:15 PM
  • Hi Jeffrey,

    Ok!! Sure.

    Just to summarize, I need to set the rows of whole sheet to some specific height dynamically during sheet addition to workbook.

    Will wait for your reply.

    Thanks,

    Harvinder


    Thursday, August 01, 2013 7:37 AM
  • Hi Harvinder

    Although you're using a VSTO Workbook, the question your have is more about how the Excel object model works. VSTO builds on the Excel object model and, for the most part, that's what your code works with. In this case, it is definitely the Excel object model. The people who know best how to work with the Excel object model congregate in the Excel for Developers forum. You might have better luck asking the question "How do I locate the first empty cell" there, than here...

    Would you like me to move the question for you?


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, August 01, 2013 2:40 PM
  • Hi Cindy,

    Thanks for suggestion.

    I had developed the logic for finding the first empty cell/row. My query remains, to set whole sheet row height.

    I tried newSheet.Rows.RowHeight = 12;  // it tried to assign all rows but it goes to inifinite loop..

    Lets see if anything is available or I will set the limit to 20000 rows.  newSheet.Range[1,20000].RowHeight = 12; 

    Thanks


    Saturday, August 03, 2013 12:06 PM
  • Hi,

    You can try setting the Rowheight of an Excel Range as follows :

    ActiveSheet.Range("A1", "C3").Rowheight = 30

    Regards

    Pradip

    Monday, August 12, 2013 3:16 AM
  • Hi Pradip,

    By selecting Column we cannot set row height for all rows. To see, open excel sheet and just select some columns and change row height, it will change only for that row. To set RowHeight for all Rows we need to select all Rows in Excel, i.e. do Ctrl A then adjust row height, it sets for all rows.

    Regards,
    Harvinder

    Monday, August 12, 2013 12:49 PM