How Do you Loop through VBE Column cells?

[Brian Connelly]

Im trying to make my code (pasted below) a little more dynamic and effective. If I don't know how many columns are being used (maybe columns A:H is used or maybe A:AU is used), I want to loop through the columns as append the Column letter into an array. I might want to loop through A:H one time and later change to loop through columns H:AB. Can anyone help me with the logic or improve on what I am trying to accomplish?

Sub ProductCleanUp()
'
' Macro1 Macro
'
Dim Col(1 To 20) As String
Dim Row As Integer
Row = 2
Col(1) = "G"
Col(2) = "H"
Col(3) = "I"
Col(4) = "J"
Col(5) = "K"
Col(6) = "L"
Col(7) = "M"
Col(8) = "N"
Col(9) = "O"
Col(10) = "P"
Col(11) = "Q"
Col(12) = "R"
Col(13) = "S"
Col(14) = "T"
Col(15) = "U"
Col(16) = "V"
Col(17) = "W"
Col(18) = "X"
Col(19) = "Y"
Col(20) = "Z"
 
Dim CurrentCell As String
Dim PriorCell As String
CurrentCell = "C" + CStr(Row)
PriorCell = "C" + CStr(Row - 1)
 
While (Range(CurrentCell).Value <> "")
        If (Range(CurrentCell).Value = Range(PriorCell).Value) Then
            Dim rangeCopy As String
            Dim rangePaste As String
            Dim i As Integer
                For i = 1 To 20
                rangeCopy = Col(i) + CStr(Row)
                rangePaste = Col(i) + CStr(Row - 1)
                    If (Range(rangeCopy).Value = "Yes") Then
                        Range(rangeCopy).Copy Range(rangePaste)
                    End If
                Next
        Dim strDeleteRow As String
        strDeleteRow = CStr(Row) + ":" + CStr(Row)
        Rows(strDeleteRow).Select
        Selection.Delete Shift:=xlUp
        End If
    Row = Row + 1
    CurrentCell = "C" + CStr(Row)
    PriorCell = "C" + CStr(Row - 1)
Wend
 
 
End Sub
 

[Rodney Roe]

Have you tried using range.offset()

for i = 0 to 19
   [C1].offset(0,i)= [C1].offset(1,i)
next
....
 

something simmilar to that. [C1] is the shortcut version of range("C1").