Finding Hidden rows

Aspose.Cells Product Family
1

HI,

Can some let me know how the following line of code in excel can be translated and achieved using Aspose.Cells. I’m using Aspose.Cells version 7.0.4.3

WS= Worksheet

UnusedCol = WS.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1

Rows = WS.Columns(UnusedCol).SpecialCells(xlCellTypeConstants).EntireRow.Address(0, 0)

Thanks

Dennison

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We think, the following article will help you. The article explains how to use Aspose.Cells API to find or search data. You can use its code and modify it as per your needs.


If you face any other issue, please feel free to post, we will like to help you further.

3

HI shakeel, i couldn't get the correct result.

I'm actually trying to find the hidden rows in excel sheet. The normal way of looping through each row and finding out if it is hidden is taking too much time, and we've received too many complaints from our clients.

That is the reason i'm trying to convert the following excel macro and achieve the same result using Aspose.

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/whats-the-method-to-find-hidden-rows-and-column/6702ab20-a74a-e011-8dfc-68b599b31bf5?tab=AllReplies#tabsv

Public Function HiddenRows(Optional WS As Worksheet) As String
Dim UnusedCol As Long, LastRow As Long, UnusedColStatus As Boolean
Dim rn As Range

If WS Is Nothing Then Set WS = ActiveSheet

UnusedCol = WS.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
Application.ScreenUpdating = False
On Error GoTo Done
With WS.Columns(UnusedCol)
UnusedColStatus = .Hidden
.Hidden = False
.Value = "X"
.SpecialCells(xlCellTypeVisible).Clear
HiddenRows = .SpecialCells(xlCellTypeConstants).EntireRow.Address(0, 0)
.Clear
.Hidden = UnusedColStatus
End With
Done:
Application.ScreenUpdating = True
End FunctionPublic Function HiddenRows(Optional WS As Worksheet) As String
Dim UnusedCol As Long, LastRow As Long, UnusedColStatus As Boolean
Dim rn As Range

If WS Is Nothing Then Set WS = ActiveSheet

UnusedCol = WS.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
Application.ScreenUpdating = False
On Error GoTo Done
With WS.Columns(UnusedCol)
UnusedColStatus = .Hidden
.Hidden = False
.Value = "X"
.SpecialCells(xlCellTypeVisible).Clear
HiddenRows = .SpecialCells(xlCellTypeConstants).EntireRow.Address(0, 0)
.Clear
.Hidden = UnusedColStatus
End With
Done:
Application.ScreenUpdating = True
End Function

4

Hi,

Thanks for your posting and using Aspose.Cells.

I think, you should iterate in a for loop and check for each row if it is hidden or not hidden. Only this way, you will be able to find hidden rows. I am afraid, there is no good solution for it at this moment.

Please check the Row.IsHidden property for your needs. The following code checks which of the first 100 rows are hidden.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\docu2.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


for (int i = 0; i < 100; i++)

{

Row r = worksheet.Cells.Rows[i];


if (r.IsHidden == true)

{

Debug.WriteLine(“Row is hidden”);

}//if

}//for

5

Is it possible to execute a custom function ( basically a macro) and get the output using aspose.

I tried the following but it doesn't work

workbook = new Workbook(TEMPLATE_EXCEL);
worksheet = workbook.Worksheets[0];
var result = worksheet.CalculateFormula("=FindHiddenRows(\"" + fileName + "\")");

where TEMPLATE_EXCEL= an xlsm file.

6

I tried settng an Addin formula but it gives me an error like:

Source array was not long enough. Check srcIndex and length, and the array's lower bounds.

The code used is as follows:

workbook = new Workbook(TEMPLATE_EXCEL);
worksheet = workbook.Worksheets[0];
formula = "=FindHiddenRows(\"" + fileName + "\")";
worksheet.Cells["A1"].SetAddInFormula(TEMPLATE_XLA, formula);
workbook.CalculateFormula();

7

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Well, you cannot use any macros or execute them using Aspose.Cells. For the error mentioned by you, could you please provide us your source project replicating this issue using the latest version: Aspose.Cells
for .NET v7.4.0.2

We will look into your issue and help you asap.

8

I’m getting the same error with the dll provided. The sample application is attached .


9

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We are afraid, macros or VBA is not supported by Aspose.Cells. However, we have logged your issue in our database.

We will look into your issue and help or advise you asap.

This issue has been logged as CELLSNET-41401.

10
Hi,

After further investigation, we come to conclusion that we can only fix the bug of setting the Add-ins formula. But, I am afraid, we cannot support calculating the Add-in Macro formula at the moment.

Thank you.

11

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and try this fix: Aspose.Cells for .NET v7.4.0.4.


We have fixed the issue of setting addin
formulas.

But we do not support calculate it.

Please implement it in
ICustomFunction.

12

The issues you have found earlier (filed as CELLSNET-41401) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
13

Hi,

I'm now able to set the Addin Formula, but still not able to execute the macro within the addin formula..

As i mentioned earlier i'm trying to find the hidden rows in excel using the following function.

Is there a way that i can simulte this funtionality using ICustomFunction feature.

Public Function HiddenRows(Optional WS As Worksheet) As String
Dim UnusedCol As Long, LastRow As Long, UnusedColStatus As Boolean
Dim rn As Range

If WS Is Nothing Then Set WS = ActiveSheet

UnusedCol = WS.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
Application.ScreenUpdating = False
On Error GoTo Done
With WS.Columns(UnusedCol)
UnusedColStatus = .Hidden
.Hidden = False
.Value = "X"
.SpecialCells(xlCellTypeVisible).Clear
HiddenRows = .SpecialCells(xlCellTypeConstants).EntireRow.Address(0, 0)
.Clear
.Hidden = UnusedColStatus
End With
Done:
Application.ScreenUpdating = True
End Function

Thanks

Dennison

14

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We are pleased to know you were able to sort out this issue. However, macros cannot be executed using Aspose.Cells.We are afraid, you cannot simulate this functionality using ICustomFunction feature.