<p>I need to simply go through all the cells in a Excel Spreadsheet and check the values in the cells.  The cells may contain text, numbers or be blank.  I am not very familiar / comfortable working with the concept of 'Range'.  Therefore, any sample codes would be greatly appreciated.  (I did try to google it, but the code snippets I found didn't quite do what I needed)</p>

<p>Thank you.</p>

[{'answer_id': 73891, 'author': 'Martin08', 'author_id': 8203, 'author_profile': 'https://Stackoverflow.com/users/8203', 'pm_score': 0, 'selected': False, 'text': '<p>In Excel VBA, this function will give you the content of any cell in any worksheet.</p>\n\n<pre><code>Function getCellContent(Byref ws As Worksheet, ByVal rowindex As Integer, ByVal colindex As Integer) as String\n    getCellContent = CStr(ws.Cells(rowindex, colindex))\nEnd Function\n</code></pre>\n\n<p>So if you want to check the value of cells, just put the function in a loop, give it the reference to the worksheet you want and the row index and column index of the cell. Row index and column index both start from 1, meaning that cell A1 will be ws.Cells(1,1) and so on.</p>\n'}, {'answer_id': 73900, 'author': 'betelgeuce', 'author_id': 366182, 'author_profile': 'https://Stackoverflow.com/users/366182', 'pm_score': 4, 'selected': True, 'text': '<pre><code>Sub CheckValues1()\n    Dim rwIndex As Integer\n    Dim colIndex As Integer\n    For rwIndex = 1 To 10\n            For colIndex = 1 To 5\n                If Cells(rwIndex, colIndex).Value &lt;&gt; 0 Then _\n                    Cells(rwIndex, colIndex).Value = 0\n            Next colIndex\n    Next rwIndex\nEnd Sub\n</code></pre>\n\n<p>Found this snippet on <a href="http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/Checksvaluesinarange10rowsby5columns.htm" rel="noreferrer">http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/Checksvaluesinarange10rowsby5columns.htm</a> It seems to be quite useful as a function to illustrate the means to check values in cells in an ordered fashion.</p>\n\n<p>Just imagine it as being a 2d Array of sorts and apply the same logic to loop through cells.</p>\n'}, {'answer_id': 73907, 'author': 'theo', 'author_id': 7870, 'author_profile': 'https://Stackoverflow.com/users/7870', 'pm_score': 2, 'selected': False, 'text': '<p>You can use a For Each to iterate through all the cells in a defined range.</p>\n\n<pre><code>Public Sub IterateThroughRange()\n\nDim wb As Workbook\nDim ws As Worksheet\nDim rng As Range\nDim cell As Range\n\nSet wb = Application.Workbooks(1)\nSet ws = wb.Sheets(1)\nSet rng = ws.Range("A1", "C3")\n\nFor Each cell In rng.Cells\n    cell.Value = cell.Address\nNext cell\n\nEnd Sub\n</code></pre>\n'}, {'answer_id': 73912, 'author': 'cori', 'author_id': 8151, 'author_profile': 'https://Stackoverflow.com/users/8151', 'pm_score': 6, 'selected': False, 'text': "<p>If you only need to look at the cells that are in use you can use:</p>\n\n<pre><code>sub IterateCells()\n\n   For Each Cell in ActiveSheet.UsedRange.Cells\n      'do some stuff\n   Next\n\nEnd Sub\n</code></pre>\n\n<p>that will hit everything in the range from A1 to the last cell with data (the bottom right-most cell)</p>\n"}, {'answer_id': 73946, 'author': 'TheXenocide', 'author_id': 8543, 'author_profile': 'https://Stackoverflow.com/users/8543', 'pm_score': 1, 'selected': False, 'text': '<p>There are several methods to accomplish this, each of which has advantages and disadvantages; First and foremost, you\'re going to need to have an instance of a Worksheet object, Application.ActiveSheet works if you just want the one the user is looking at.</p>\n\n<p>The Worksheet object has three properties that can be used to access cell data (Cells, Rows, Columns) and a method that can be used to obtain a block of cell data, (get_Range).</p>\n\n<p>Ranges can be resized and such, but you may need to use the properties mentioned above to find out where the boundaries of your data are. The advantage to a Range becomes apparent when you are working with large amounts of data because VSTO add-ins are hosted outside the boundaries of the Excel application itself, so all calls to Excel have to be passed through a layer with overhead; obtaining a Range allows you to get/set all of the data you want in one call which can have huge performance benefits, but it requires you to use explicit details rather than iterating through each entry.</p>\n\n<p><a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=767501&amp;SiteID=1" rel="nofollow noreferrer">This MSDN forum post</a> shows a VB.Net developer asking a question about getting the results of a Range as an array</p>\n'}, {'answer_id': 74006, 'author': 'w4ik', 'author_id': 4232, 'author_profile': 'https://Stackoverflow.com/users/4232', 'pm_score': 1, 'selected': False, 'text': '<p>You basically can loop over a Range</p>\n\n<p>Get a sheet</p>\n\n<pre><code>myWs = (Worksheet)MyWb.Worksheets[1];\n</code></pre>\n\n<p>Get the Range you\'re interested in If you really want to check every cell use Excel\'s limits </p>\n\n<blockquote>\n  <p>The Excel 2007 "Big Grid" increases\n  the maximum number of rows per\n  worksheet from 65,536 to over 1\n  million, and the number of columns\n  from 256 (IV) to 16,384 (XFD).\n  from here <a href="http://msdn.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_BigGridIncreasedLimitsExcel" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_BigGridIncreasedLimitsExcel</a></p>\n</blockquote>\n\n<p>and then loop over the range</p>\n\n<pre><code>        Range myBigRange = myWs.get_Range("A1", "A256");\n\n        string myValue;\n\n        foreach(Range myCell in myBigRange )\n        {\n            myValue = myCell.Value2.ToString();\n        }\n</code></pre>\n'}, {'answer_id': 74012, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>My VBA skills are a little rusty, but this is the general idea of what I\'d do.<br>\nThe easiest way to do this would be to iterate through a loop for every column:</p>\n\n<pre><code>public sub CellProcessing()\non error goto errHandler\n\n    dim MAX_ROW as Integer   \'how many rows in the spreadsheet\n    dim i as Integer\n    dim cols as String\n\n    for i = 1 to MAX_ROW\n        \'perform checks on the cell here\n        \'access the cell with Range("A" &amp; i) to get cell A1 where i = 1\n    next i\n\nexitHandler:\n    exit sub\nerrHandler:\n    msgbox "Error " &amp; err.Number &amp; ": " &amp; err.Description\n    resume exitHandler\nend sub\n</code></pre>\n\n<p>it seems that the color syntax highlighting doesn\'t like vba, but hopefully this will help somewhat (at least give you a starting point to work from).</p>\n\n<ul>\n<li>Brisketeer</li>\n</ul>\n'}, {'answer_id': 74209, 'author': 'Cory Engebretson', 'author_id': 3406, 'author_profile': 'https://Stackoverflow.com/users/3406', 'pm_score': 2, 'selected': False, 'text': '<p>For a VB or C# app, one way to do this is by using Office Interop.  This depends on which version of Excel you\'re working with.</p>\n\n<p>For Excel 2003, this MSDN article is a good place to start.\n<a href="http://msdn.microsoft.com/en-us/library/aa537184(office.11).aspx" rel="nofollow noreferrer">Understanding the Excel Object Model from a Visual Studio 2005 Developer\'s Perspective </a></p>\n\n<p>You\'ll basically need to do the following:</p>\n\n<ul>\n<li>Start the Excel application.</li>\n<li>Open the Excel workbook.</li>\n<li>Retrieve the worksheet from the workbook by name or index.</li>\n<li>Iterate through all the Cells in the worksheet which were retrieved as a range.</li>\n<li>Sample (untested) code excerpt below for the last step.</li>\n</ul>\n\n<pre><code>\n    Excel.Range allCellsRng;\n    string lowerRightCell = "IV65536";\n    allCellsRng = ws.get_Range("A1", lowerRightCell).Cells;\n    foreach (Range cell in allCellsRng)\n    {\n        if (null == cell.Value2 || isBlank(cell.Value2))\n        {\n          // Do something.\n        }\n        else if (isText(cell.Value2))\n        {\n          // Do something.\n        }\n        else if (isNumeric(cell.Value2))\n        {\n          // Do something.\n        }\n    }\n</code></pre>\n\n<p>For Excel 2007, try <a href="http://msdn.microsoft.com/en-us/library/bb979621.aspx" rel="nofollow noreferrer">this MSDN reference</a>.</p>\n'}, {'answer_id': 87577, 'author': 'Jon Fournier', 'author_id': 5106, 'author_profile': 'https://Stackoverflow.com/users/5106', 'pm_score': 3, 'selected': False, 'text': '<p>If you\'re just looking at values of cells you can store the values in an array of variant type.  It seems that getting the value of an element in an array can be much faster than interacting with Excel, so you can see some difference in performance using an array of all cell values compared to repeatedly getting single cells.</p>\n\n<pre><code>Dim ValArray as Variant\nValArray = Range("A1:IV" &amp; Rows.Count).Value\n</code></pre>\n\n<p>Then you can get a cell value just by checking ValArray( row , column )</p>\n'}]
['https://Stackoverflow.com/questions/73785', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5899/']