Load a range of non blank excel cells into an array in VBA

The code below will allow you to

  1. Define a range of cells in excel that contain data ( are non-blanks)
  2. Load the data into an array
  3. Loop through the data (… process the records as needed….)
Sub read_nonblank_cells()
   firstrow = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlNext).Row
   firstcolumn = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlNext).Column
   lastrow = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
   lastcolumn = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
   Dim arrData() As Variant 
   arrData = ActiveSheet.Range(Cells(firstrow, firstcolumn), Cells(lastrow, lastcolumn)).Value
   countarray = UBound(arrData)
   For i = 1 To countarray
      ' Do something with your array
      ' each column of data could be addressed with its index
      ' as follows: arrData(i,n)
      ' where n is your column number

Next i End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s