UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Looping Through A Range Of Cells In VBA    
Looping Through A Range Of Cells In VBA

There are two methods for looping to cells in VBA. I call them counter and pointer.

The counter method works best when the boundaries of the range are defined or a stopping point such as a blank cell can be defined

The pointer range works best with named ranges.

Both methods will be demonstrated using the data range in the table below. We will be looking at Column C (Last Name). This column is also defined as a named range: Last_Name =OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$C:$C)-1,1)

Image:File01A.jpg

Counter Method 1A (Based on Number of Rows)

It is easy to see that the data is contained in rows 2 to 14. The last row could have been calculated using =COUNTA(C:C).

This code uses a For statement.

CODE

Sub CopyNames1()
Dim RowNum As Long, NumRows As Long
Dim sh1 As Excel.Worksheet, sh2 As Excel.Worksheet

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")

' Clear the old data, create headers and starting row on sheet 2
sh2.Cells.ClearContents
sh2.Range("A1") = "Name"
RowNum = 2
NumRows = Application.WorksheetFunction.CountA(Sheets("Sheet1").Range("C:C"))

For RowNum = 2 To NumRows
   sh2.Cells(RowNum, 1) = sh1.Cells(RowNum, 3)
Next

End Sub

Counter Method 1B (Based on a Cell that is a Stopping Point)

This code uses a While statement.

CODE

Sub CopyNames2()
Dim RowNum As Long
Dim sh1 As Excel.Worksheet, sh2 As Excel.Worksheet

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet3")

' Clear the old data, create headers and starting row on sheet 2
sh2.Cells.ClearContents
sh2.Range("A1") = "Name"
RowNum = 2


While Len(sh1.Cells(RowNum, 3)) > 0
   sh2.Cells(RowNum, 1) = sh1.Cells(RowNum, 3)
   RowNum = RowNum + 1
Wend

End Sub

Pointer Method (Based on Named Range)

This code uses a For Each statement.

CODE

Sub CopyNames3()
Dim RowNum As Long
Dim sh1 As Excel.Worksheet, sh2 As Excel.Worksheet
Dim cl As Range

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet4")

' Clear the old data, create headers and starting row on sheet 2
sh2.Cells.ClearContents
sh2.Range("A1") = "Name"
RowNum = 2

For Each cl In Range("Last_Name")
   sh2.Cells(RowNum, 1) = cl.Value
   RowNum = RowNum + 1
Next

End Sub
Edit Discussion
Custom Search


Thank you for your support!
This page has been accessed 2,635 times.  This page was last modified 15:49, 21 February 2014 by dflak.   Disclaimers