0

I copied a bunch of tables from a PDF file to Excel. these tables contain empty cells. I need to write a code that'll be able to recognize and count the number of empty cells in each column of each table (I am saving one table per sheet). I posted the following question IsNumeric function returning true for an empty cell and thought I had the answer by calculating the length of the trimmed value of each cell, but it turns out that doesn't solve it. The length function returns a value of 5 for those empty cells. and a value of 5 for many other cells that have values.

When I click ctrl + G and select blanks those empty cells are not highlighted. Only cells in columns that are entirely empty are highlighted.

Looks like I had a problem referencing my sheets. The command isnumber returns false, so that sort of solves my problem. but using len(clean()) still returns a number bigger than zero. For now I can make do with the isnumber function. but I'd be interested to know what sort of value is stored in these cells.

Community
  • 1
  • 1
user2681358
  • 99
  • 1
  • 1
  • 8
  • can you upload one spreadsheet of your workbook? If the number returned from `Len(cell)` equals 5 that means there definitely are 5 characters in that cell. If they are not visible the font color may be set to white. Based on your other question I can't think of any other relevant alternative. You can add a breakpoint while iterating over cells in debug-mode and check the value of each cell by using `cell.value`,`cell.value2`,`cell.text` –  Aug 15 '13 at 09:08
  • when I click on a cell the function bar is empty. and when I debug to check cell value "MsgBox cell.value" it's also empty. – user2681358 Aug 18 '13 at 04:23
  • `Clean` only removes characters with ascii code < 32. Other non-printing characters exist, eg non-break space (code 160). Clean on its own will not remove these. – chris neilsen Aug 18 '13 at 05:18
  • I see, that makes since, but still how do I identify those empty spaces? the function code(cell#) returns 32 for both the empty and non empty cells – user2681358 Aug 18 '13 at 08:40

1 Answers1

0

My best guess here is that the cells actually contain non-printable characters such as carriage returns.

You can easily check this by using the following formula: =LEN(CLEAN(A1)) which should return a different value compared to just =LEN(A1)

markblandford
  • 3,153
  • 3
  • 20
  • 28