How to loop through first Columns then Rows?

E

elmnas

Board Regular
Joined
Feb 20, 2015
Messages
206
Hi guys I need a loop that loops through first

column A and then b etc.

then takes rows?


Could someone help me with a script?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Welcome to the forum!

Here're a whole bunch of loops to process a range in different directions - I think you need a variation on Sub ProcessRange05_ColAscRowAsc()

Code: 
'-----------------------------------------------------------------------
'Name:      Code - Alternative For Each Loops.xlsm
'Date:      2013-07-08
'Module:    Mod_01_Procedures
'Source:
'Comments:  The range "Data" is B3:D5 and contains the following values:
'           1    2    3
'           4    5    6
'           7    8    9
'-----------------------------------------------------------------------


'=========================================
'Variables for Range processing procedures
'=========================================


Public MyRange As Range
Public MyRow As Range
Public MyCol As Range
Public MyCell As Range


Public MyRowTotal As Long
Public MyRowCount As Long
Public MyColTotal As Long
Public MyColCount As Long
Public MyCellTotal As Long
Public MyCellCount As Long


Sub ProcessRange01_RowAscColAsc() 'returns 1,2,3,4,5,6,7,8,9 - Left To Right, Top To Bottom
    Set MyRange = Range("Data")
    For Each MyCell In MyRange
        MsgBox ("Address: " & MyCell.Address & Chr(10) & "Value: " & MyCell.Value)
        'Do stuff with MyCell
    Next
End Sub


Sub ProcessRange02_RowAscColDesc() 'returns 3,2,1,6,5,4,9,8,7 - Right To Left, Top To Bottom
    Set MyRange = Range("Data")
    For Each MyRow In MyRange.Rows
        MyRowTotal = MyRange.Rows.Count
        For MyRowCount = MyRowTotal To 1 Step -1
            MsgBox ("Address: " & MyRow.Cells(MyRowCount).Address & Chr(10) & "Value: " & _
                MyRow.Cells(MyRowCount).Value)
                'Do stuff with MyRow.Cells(MyRowCount)
        Next
    Next
End Sub


Sub ProcessRange03_RowDescColAsc()  'returns 7,8,9,4,5,6,1,2,3 - Left To Right, Bottom To Top
    Set MyRange = Range("Data")
    MyRowTotal = MyRange.Rows.Count
    For MyRowCount = MyRowTotal To 1 Step -1
        For Each MyCol In MyRange.Columns
            MsgBox ("Address: " & MyCol.Cells(MyRowCount).Address & Chr(10) & _
                "Value: " & MyCol.Cells(MyRowCount).Value)
                'Do stuff with MyCol.Cells(MyRowCount)
        Next
    Next
End Sub


Sub ProcessRange04_RowDescColDesc() 'returns 9,8,7,6,5,4,3,2,1 - Right To Left, Bottom To Top
    Set MyRange = Range("Data")
    MyCellTotal = MyRange.Cells.Count
    For MyCellCount = MyCellTotal To 1 Step -1
        MsgBox ("Address: " & MyRange.Cells(MyCellCount).Address & Chr(10) & _
            "Value: " & MyRange.Cells(MyCellCount).Value)
            'Do stuff with MyRange.Cells(MyCellCount)
    Next
End Sub


Sub ProcessRange05_ColAscRowAsc() 'returns 1,4,7,2,5,8,3,6,9 - Top To Bottom, Left To Right
    Set MyRange = Range("Data")
    For Each MyCol In MyRange.Columns
        For Each MyCell In MyCol.Cells
            MsgBox ("Address: " & MyCell.Address & Chr(10) & "Value: " & MyCell.Value)
            'Do stuff with MyCell
        Next
    Next
End Sub


Sub ProcessRange06_ColAsc_RowDesc() 'returns 7,4,8,8,5,2,9,6,3 - Bottom To Top, Left To Right
    Set MyRange = Range("Data")
    For Each MyCol In MyRange.Columns
        MyCellTotal = MyCol.Cells.Count
        For MyCellCount = MyCellTotal To 1 Step -1
            MsgBox ("Address: " & MyCol.Cells(MyCellCount).Address & Chr(10) & _
                "Value: " & MyCol.Cells(MyCellCount).Formula)
               'Do stuff with MyCol.Cells(MyCellCount)
        Next
    Next
End Sub


Sub ProcessRange07_ColDesc_RowAsc() 'returns 3,6,9,2,5,8,1,4,7 - Top To Bottom, Right To Left
    Set MyRange = Range("Data")
    MyColTotal = MyRange.Columns.Count
    For MyColCount = MyColTotal To 1 Step -1
        MyCellTotal = MyRange.Columns(MyColCount).Cells.Count
        For MyCellCount = 1 To MyCellTotal
            MsgBox ("Address: " & MyRange.Columns(MyColCount).Cells(MyCellCount).Address & Chr(10) & _
                "Value: " & MyRange.Columns(MyColCount).Cells(MyCellCount).Formula)
               'Do stuff with MyRange.Columns(MyColCount).Cells(MyCellCount)
        Next
    Next
End Sub


Sub ProcessRange08_ColDesc_RowDesc() 'returns 9,6,3,8,5,2,7,4,1 - Bottom To Top, Right To Left
    Set MyRange = Range("Data")
    MyColTotal = MyRange.Columns.Count
    For MyColCount = MyColTotal To 1 Step -1
        MyCellTotal = MyRange.Columns(MyColCount).Cells.Count
        For MyCellCount = MyCellTotal To 1 Step -1
            MsgBox ("Address: " & MyRange.Columns(MyColCount).Cells(MyCellCount).Address & Chr(10) & _
                "Value: " & MyRange.Columns(MyColCount).Cells(MyCellCount).Formula)
              'Do stuff with MyRange.Columns(MyColCount).Cells(MyCellCount)
        Next
    Next
End Sub


'Function to display the formula in a cell
Function ShowFormula(MyRange As Range) As String
    ShowFormula = MyRange.Formula
End Function

Hope this helps

Pete
 
Upvote 0
Perhaps.
Code: 
Set rng = Range("A1:D20")

   For Each rw In rng.Rows
       For Each cl in rw.Columns
           MsgBox cl.Address
       Next cl
   Next rw
 
Upvote 0
PeteRooneyHome said:
Welcome to the forum!

Here're a whole bunch of loops to process a range in different directions - I think you need a variation on Sub ProcessRange05_ColAscRowAsc()

Code: 
'-----------------------------------------------------------------------
'Name:      Code - Alternative For Each Loops.xlsm
'Date:      2013-07-08
'Module:    Mod_01_Procedures
'Source:
'Comments:  The range "Data" is B3:D5 and contains the following values:
'           1    2    3
'           4    5    6
'           7    8    9
'-----------------------------------------------------------------------


'=========================================
'Variables for Range processing procedures
'=========================================


Public MyRange As Range
Public MyRow As Range
Public MyCol As Range
Public MyCell As Range


Public MyRowTotal As Long
Public MyRowCount As Long
Public MyColTotal As Long
Public MyColCount As Long
Public MyCellTotal As Long
Public MyCellCount As Long


Sub ProcessRange01_RowAscColAsc() 'returns 1,2,3,4,5,6,7,8,9 - Left To Right, Top To Bottom
    Set MyRange = Range("Data")
    For Each MyCell In MyRange
        MsgBox ("Address: " & MyCell.Address & Chr(10) & "Value: " & MyCell.Value)
        'Do stuff with MyCell
    Next
End Sub


Sub ProcessRange02_RowAscColDesc() 'returns 3,2,1,6,5,4,9,8,7 - Right To Left, Top To Bottom
    Set MyRange = Range("Data")
    For Each MyRow In MyRange.Rows
        MyRowTotal = MyRange.Rows.Count
        For MyRowCount = MyRowTotal To 1 Step -1
            MsgBox ("Address: " & MyRow.Cells(MyRowCount).Address & Chr(10) & "Value: " & _
                MyRow.Cells(MyRowCount).Value)
                'Do stuff with MyRow.Cells(MyRowCount)
        Next
    Next
End Sub


Sub ProcessRange03_RowDescColAsc()  'returns 7,8,9,4,5,6,1,2,3 - Left To Right, Bottom To Top
    Set MyRange = Range("Data")
    MyRowTotal = MyRange.Rows.Count
    For MyRowCount = MyRowTotal To 1 Step -1
        For Each MyCol In MyRange.Columns
            MsgBox ("Address: " & MyCol.Cells(MyRowCount).Address & Chr(10) & _
                "Value: " & MyCol.Cells(MyRowCount).Value)
                'Do stuff with MyCol.Cells(MyRowCount)
        Next
    Next
End Sub


Sub ProcessRange04_RowDescColDesc() 'returns 9,8,7,6,5,4,3,2,1 - Right To Left, Bottom To Top
    Set MyRange = Range("Data")
    MyCellTotal = MyRange.Cells.Count
    For MyCellCount = MyCellTotal To 1 Step -1
        MsgBox ("Address: " & MyRange.Cells(MyCellCount).Address & Chr(10) & _
            "Value: " & MyRange.Cells(MyCellCount).Value)
            'Do stuff with MyRange.Cells(MyCellCount)
    Next
End Sub


Sub ProcessRange05_ColAscRowAsc() 'returns 1,4,7,2,5,8,3,6,9 - Top To Bottom, Left To Right
    Set MyRange = Range("Data")
    For Each MyCol In MyRange.Columns
        For Each MyCell In MyCol.Cells
            MsgBox ("Address: " & MyCell.Address & Chr(10) & "Value: " & MyCell.Value)
            'Do stuff with MyCell
        Next
    Next
End Sub


Sub ProcessRange06_ColAsc_RowDesc() 'returns 7,4,8,8,5,2,9,6,3 - Bottom To Top, Left To Right
    Set MyRange = Range("Data")
    For Each MyCol In MyRange.Columns
        MyCellTotal = MyCol.Cells.Count
        For MyCellCount = MyCellTotal To 1 Step -1
            MsgBox ("Address: " & MyCol.Cells(MyCellCount).Address & Chr(10) & _
                "Value: " & MyCol.Cells(MyCellCount).Formula)
               'Do stuff with MyCol.Cells(MyCellCount)
        Next
    Next
End Sub


Sub ProcessRange07_ColDesc_RowAsc() 'returns 3,6,9,2,5,8,1,4,7 - Top To Bottom, Right To Left
    Set MyRange = Range("Data")
    MyColTotal = MyRange.Columns.Count
    For MyColCount = MyColTotal To 1 Step -1
        MyCellTotal = MyRange.Columns(MyColCount).Cells.Count
        For MyCellCount = 1 To MyCellTotal
            MsgBox ("Address: " & MyRange.Columns(MyColCount).Cells(MyCellCount).Address & Chr(10) & _
                "Value: " & MyRange.Columns(MyColCount).Cells(MyCellCount).Formula)
               'Do stuff with MyRange.Columns(MyColCount).Cells(MyCellCount)
        Next
    Next
End Sub


Sub ProcessRange08_ColDesc_RowDesc() 'returns 9,6,3,8,5,2,7,4,1 - Bottom To Top, Right To Left
    Set MyRange = Range("Data")
    MyColTotal = MyRange.Columns.Count
    For MyColCount = MyColTotal To 1 Step -1
        MyCellTotal = MyRange.Columns(MyColCount).Cells.Count
        For MyCellCount = MyCellTotal To 1 Step -1
            MsgBox ("Address: " & MyRange.Columns(MyColCount).Cells(MyCellCount).Address & Chr(10) & _
                "Value: " & MyRange.Columns(MyColCount).Cells(MyCellCount).Formula)
              'Do stuff with MyRange.Columns(MyColCount).Cells(MyCellCount)
        Next
    Next
End Sub


'Function to display the formula in a cell
Function ShowFormula(MyRange As Range) As String
    ShowFormula = MyRange.Formula
End Function

Hope this helps

Pete
Click to expand...

Awesome ****!"

solved!!!!!

thanks cheers.
 
Upvote 0
You must log in or register to reply here.

Similar threads

2
  • Question
Hide columns based on IF
Replies
6
Views
134
kevin9999
K
B
  • Solved
Do Until loop nested in For loop, How advance to the "Next x" in the For Loop
Replies
4
Views
265
CephasOz
CephasOz
R
  • Question
Turn Excel VBA Object Browser into a 4-column long list
2 3 4
Replies
31
Views
945
John_w
J
B
  • Solved
VBA Loop Through an Array
Replies
6
Views
403
Peter_SSs
Peter_SSs
R
  • Question
VBA Loop Through Data Validation Drop Down List
Replies
5
Views
400
rlv01
R

Forum statistics

Threads
1,215,223
Messages
6,123,714
Members
449,118
Latest member
MichealRed

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top