CODEWITHSUNDEEP
excelvba
Ilman Arif
Ilman Arif

Reputation: 1

Loop columns using VBA

I want to loop through columns.

I'm not sure about the range. Should I use normal range where we have the alphabets as columns?

I tried different codes. For example: 

Sub copytest()

Dim x As Workbook
Dim j As Integer, i As Integer

Application.ScreenUpdating = False

Workbooks.Open ("D:\test\COMP.xlsx")

i = 3

For j = 3 To 14

    Sheets("Compliance").Range(Cells(18, i), Cells(30, i)).Copy
    Windows("KP.xlsm").Activate
    Sheets("MOH").Range(Cells(12, j), Cells(24, j)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

    i = i + 2

Next j

x.Close
End Sub

Upvotes: 0

Views: 3258

Answers (3)

Rogelio Garza
Rogelio Garza

Reputation: 1

Sub Columns()

Dim rng As Range
Dim col As Range
Sheets("Sheet1").Select 'make shure its in the correct sheet

Set rng = Range("C3:G6") 'select the range of the whole table


For Each col In rng.Columns 'this performs a tasks column by column
    'Do Something
    col.Select
    Selection.RemoveDuplicates Columns:=1, Header:=xlNo
    'end something

Next col 'move to next column

End Sub

Upvotes: 0

user3598756
user3598756

Reputation: 29421

an alternative for looping though columns is a For Each cell in my1RowRange loop, where

  • cell is a single-cell Range object that loops over my1RowRange Range

  • my1RowRange must be a 1-Row Range

besides that, your code has some major flaws:

  • your range references use simple Cells(...) reference without any explicit worksheet and workbook reference before them

    so that they keep referencing the active worksheet in the active workbook

    and this is no good since during your loop you only seem to keep changing the right workbook while you're actually only changing it once at the beginning (Windows("KP.xlsm").Activate) and then it always remains the only referenced workbook

  • avoid Activate/ActiveXXX (as well as Select/Selection) coding, since

    • it's error prone, like it proved to be right here, since it most likely lead you to loose control over actual active workbook/worksheet

    • it's time consuming and gets screen flickerings

so consider the following refactoring of your code:

Option Explicit

Sub copytest()
    Dim i As Long
    Dim compWb As Workbook
    Dim compRng As Range, cell As Range

    Set compWb = Workbooks.Open "D:\test\COMP.xlsx" '<--| set the workbook to open to a specific variable of 'Workbook' type

    With compWb.Worksheets("Compliance") '<--| refer to "compliance" worksheet of the "right" (you're explicitly referencing it!) workbook
        Set compRng = .Range(.Cells(18, 3), .Cells(30, 3)) '<--| set the "source" range: all the dots means we're referencing the object after the last "With" statement
    End With

    i = 3
    With Workbooks("KP.xlsm").Worksheets("MOH") '<--| refer to "MOH" worksheet of "KP" workbook
        For Each cell In .Range(.Cells(12, 3), .Cells(12, 14)) '<--| loop through cells of a 1-row range of the referenced worksheet: this means looping through columns!
            cell.Resize(13).Value = compRng.Offset(, i - 3).Value '<--| paste values while offsetting the "souce" range columns (first iteration with offset=0)
            i = i + 2
        Next cell
    End With

    compWb.Close '<--| close the opened workbook
End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149277

You can use numbers for columns. Let's say you want to loop through first 5 rows and first 5 columns then you can write it as 

For i = 1 To 5 '<~~~ Rows
    For j = 1 To 5 '<~~~ Columns
        With ThisWorkbook.Cells(i, j)
            '~~> Do something
        End With
    Next j
Next i

In Excel 2013, the last column is XFD which is column number 16384.

A - 1
B - 2
C - 3 
.
.
and so on...
XFD - 16384

If you would like to loop using Column Names then you can use it as such

Dim ColName As String

For i = 1 To 5 '<~~~ Rows
    For j = 1 To 5 '<~~~ Columns
       ColName  = Split(Cells(, j).Address, "$")(1)
        With ThisWorkbook.Range(ColName & i)
            '~~> Do something
        End With
    Next j
Next i

Upvotes: 2

Related Questions