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 do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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

R
  • Solved
Loop through Rows
Replies
4
Views
295
rollingzep
R
JeffGrant
  • Solved
Looking for a VBA loop suggestion.
Replies
2
Views
188
JeffGrant
JeffGrant
H
  • Solved
Macro to improve Optimisation
Replies
3
Views
153
Alex Blakenburg
A
E
  • Solved
How to Loop through code
Replies
3
Views
291
dreid1011
dreid1011
V
  • Question Question
VBA help - find string between 2nd and 3rd occurrence of a character.
Replies
3
Views
267
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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