How to iterate through columns?

  • Thread starter Thread starter axwack
  • Start date Start date
A

axwack

I have a particular issue with columns.

I have 4 columns. I need to iterate through the 4 columns (say H - K).
The values in the columns are put there from a database record set.
The record set, therefore, has a row length that could be variable,
depending on the number of records.

how can I iterate through 1-4 columns or any columns with data and
select the data from the column and get it's address?
 
You can adapt the following code to meet your needs:

Sub AAA()
Dim LastRow As Long
Dim ColNdx As Long
Dim RowNdx As Long
Dim V As Variant
Dim R As Range
Dim Addr As String

Const START_COL = 8 '<<<< CHANGE
Const START_ROW = 10 '<<<< CHANGE
LastRow = Cells(Rows.Count, START_COL).End(xlUp).Row

For RowNdx = START_ROW To LastRow
For ColNdx = START_COL To START_COL + 4
'''''''''''''''''''''''''
' set the range
'''''''''''''''''''''''''
Set R = Cells(RowNdx, ColNdx)
'''''''''''''''''''''''''
' get the address
'''''''''''''''''''''''''
Addr = R.Address
'''''''''''''''''''''''''
' get the value
'''''''''''''''''''''''''
V = R.Value
'''''''''''''''''''''''''
' do something with V
'''''''''''''''''''''''''
Debug.Print V
Next ColNdx
Next RowNdx
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Back
Top