Finding the cell numbers of the blank cells in a coulmn

Help
1

Can anyone help me to get the cell numbers of the blank cells in the column What i want is to calculate sum of the cells of the column D and show the total in the first blank Cell of the column for each acc …so D9 cell should have sum of cells d2 to d8 D

image
image1730×2307 1.84 MB

2

Hello.

FYI, your computer probably has the “Snipping Tool” software that you can easily use to create screenshots :stuck_out_tongue:

There are a few ways you can get the cell number.

—one way is to use Invoke VBA. I can’t say I have examples of this on me to access cell information directly, but it would basically be like if you wrote a macro in Excel.

—other ways would involve executing a macro or vbscript file… I will not recommend this method.

—another way possibly is to Extract the text from the Address bar where the Cell number is shown in top left corner. This would probably only work if you can select the cell using Find or something.

—the other solution would be to use a data table, which is usually pretty reliable and simpler logic. Assuming your table starts on row 1, you can simply find the row number by using dt1.Rows.IndexOf(row) <— row being the variable used within a for each… the column can be found by using similar code but you just need to convert it to a letter, for example, Convert.ToChar(3+65) <— 3 being the index of the column… so the cell range would basically be:

Convert.ToChar(3+65)+(dt1.Rows.IndexOf(row)+1).ToString

Hope that helps.

Regards.

1 Like
3

The empty cell or row ligic doesnot seem to work for me…so i planning to iterate the datatable and check the blank row if it is blank store counter variable in a array…but checking for blank cell/row does not seem to work…any idea on how to check whether a row of a datable is blankn or not?

4

Well it depends, because if you want to know if the row is entirely blank then that’s different then wanting to check if only a particular cell is blank, which both might work anyway.

If you have a DataRow variable, let’s call row.
To check entire row you can look at the items as an array. For example:
String.Join("", row.ItemArray.Select(Function(x) x.ToString.Trim) ) = ""
I used a .Select() to trim the spaces off of any cell that might contain a space. Then, just join the array together and use it in the condtion.

To check only an individual cell, then just use
row("column").ToString.Trim = ""

So, then you would place this condition in the for loop.

For each row in dt1
    If String.Join("", row.ItemArray.Select(Function(x) x.ToString.Trim) ) = ""
        Assign...

I hope this helps.

Also, please provide any errors you receive or describe its actions when it doesn’t work.

Regards.

5

Hello,

Thank you for your inquiry

I would suggest you to use a read range activity and a for each and iterate,

For each row in dataTable1
If row(0).ToString.Trim = "1" 
// assign the dataTable1.rows.IndexOf(row).toString - on this same true:
If row(0).ToString.Trim is not  ""
// assign the dataTable1.rows.IndexOf(row-1).toString - on this same true:  
 // here you have the first and the last cells with content, yet you have to sum each line 
 //and voila

let us know.

6

https://youtu.be/4MrBGQ0S9sA

Refer this link