read hidden cells; vba

  • Thread starter Thread starter cate
  • Start date Start date
C

cate

I loop thru a range of cells on a sheet and generate a checksum.
Hidden cells are apparently not read. Is there an “omnipotent”
setting I can make to just make reads work, or do I have to test the
value of hidden, turn it off, read it, and return it to hidden?

Thank you.
 
Cate,

Post your code - hiding cells affects some functions and not others.

HTH,
Bernie
MS Excel MVP


I loop thru a range of cells on a sheet and generate a checksum.
Hidden cells are apparently not read. Is there an “omnipotent”
setting I can make to just make reads work, or do I have to test the
value of hidden, turn it off, read it, and return it to hidden?

Thank you.
 
Cate,

Post your code - hiding cells affects some functions and not others.
Click to expand...

Set mySheet = Application.ThisWorkbook.Worksheets("CoolantGroup")
Set rgHome = mySheet.Cells(1, 1)

colEnd = INITIAL_SUBMISSION_COLS
rowEnd = INITIAL_SUBMISSION_ROWS
barrel = 1#
For colIndex = 1 To colEnd Step 1
For rowIndex = 1 To rowEnd Step 1
cellText = rgHome.offset(rowIndex, colIndex).Text
checksum = hashit(cellText, checksum)
Next ' row
Next ' col
 
Cate,

How are you "hiding" the cells? Nothing in your code is apparently wrong - unless you are applying
a custom format of ;;; to hide the cell.

But I would change

cellText = rgHome.offset(rowIndex, colIndex).Text

to

cellText = Format(rgHome.offset(rowIndex, colIndex).Value,"some appropriate format string")

which should remove any formatting considerations.

Also, you should note that your code is looping through cells starting at B2, not A1, which may or
may not matter.

HTH,
Bernie
MS Excel MVP


Cate,

Post your code - hiding cells affects some functions and not others.
Click to expand...

Set mySheet = Application.ThisWorkbook.Worksheets("CoolantGroup")
Set rgHome = mySheet.Cells(1, 1)

colEnd = INITIAL_SUBMISSION_COLS
rowEnd = INITIAL_SUBMISSION_ROWS
barrel = 1#
For colIndex = 1 To colEnd Step 1
For rowIndex = 1 To rowEnd Step 1
cellText = rgHome.offset(rowIndex, colIndex).Text
checksum = hashit(cellText, checksum)
Next ' row
Next ' col
 
Also, you should note that your code is looping through cells starting at B2, not A1, which may or
may not matter.

HTH,
Bernie
MS Excel MVP
Click to expand...

DOH!


And yes, there are cells that are purposely hidden on the protected
sheet.


I assume the format construct will allow me to read these 'hidden'
cells, or am I out of luck?

Thanks yet again.
 
cate,

I'm still not sure why you are not able to read those cells - what is their formatting? How are they
hidden? And yes, you can always read the Value of a cell - no matter what.

HTH,
Bernie
MS Excel MVP
 
Beware of the differences between cells hidden thru filtering or hidden using
the Hide option. With the latter they are available to manipulate in
interactive mode. And guess what, in macro mode you can also manipulate cells
hidden thru filtering - NOT THE WAY TO GO!
 
Back
Top