Hiding Rows in an Excel Worksheet programmatically through automation.

[sumitrishi]

Need to hide all the rows till the end that dont have any data in an excel worksheet. Please sugest how should I do it .

[Michael_Munroe]

I suggest you use the UsedRange method to determine the size of the data and the End method to jump to the last row. Then use the Range(Ax,Ay) followed by EntireRow and then set the property Hidden to True.

Here is the Visual Basic code:
Sub HideEmptyRows()
Dim EmptyRow As Integer
EmptyRow = ActiveSheet.UsedRange.Rows.Count + 1
Range(Cells(EmptyRow, 1), Cells(EmptyRow, 1).End(xlDown)).EntireRow.Hidden = True
End Sub

You can simplify the process in LV by using result from the UsedRange.Rows.Count as an integer, add 1, and then feed a string formatted as "Axxx" into the Cells(EmptyRow,1) parameters for the Range.

Michael
www.abcdefirm.com

[srini]

Hi Michael,
I used the UsedRange.Rows/Columns.count to get the number of columns and rows that are used in my excel file. But I don't get how to wire "Axxx" into the cells(EmptyRow,1) paraneters for the Range. In the Range, when I select Cells from the methods available, I am getting a number of other parameters. And I don't know to which parameter I should wire the row/column count and the "Axxx" string. Could you please help me.

Thanx & Regards,
Srini.