Iterating through all the cells in Excel VBA or VSTO 2005

vba excel vsto
Ryan Liang picture Ryan Liang · Sep 16, 2008 · Viewed 113.6k times · Source

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)

Thank you.

Answer

cori picture cori · Sep 16, 2008

If you only need to look at the cells that are in use you can use:

sub IterateCells()

   For Each Cell in ActiveSheet.UsedRange.Cells
      'do some stuff
   Next

End Sub

that will hit everything in the range from A1 to the last cell with data (the bottom right-most cell)

Related questions

How to unit test Excel VBA code

Does anyone have any experience with unit testing Excel VBA code? I want to introduce unit tests into some legacy Excel VBA code as painlessly as possible. One idea I had would be to use VSTO to call code from …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More