Avatar of tommym121
tommym121Flag for Canada asked on

How to convert entirecolumn into a collection

   For Each Cell In ActiveCell.EntireColumn
          ' Do something on each cell in the column
    Next Cell
   
But this code only go through once, it give the cell the entire column as an array.
How can I change my code so I can iterate each cell in the column that have data
Visual Basic.NETMicrosoft ExcelVisual Basic Classic

Avatar of undefined
Last Comment
tommym121

8/22/2022 - Mon
PagodNaUtak

For Each Cell In ActiveCell.EntireColumn
          ' Do something on each cell in the column
          If not Cell.Value = nothing Then
         
          End If
 Next Cell
karthika_cts

Here are some ways to loop through the cells in a column
-------------------------------------------------------------------
For Each cell In Columns("A:A")  

     msgbox cell.Text

 Next


-------------------------------------------------------------------

With oSheet
        For lRow = 1 To .UsedRange.Rows.Count
            sKey = Trim$(.Cells(lRow, 1).Value)
            msgbox sKey
        Next lRow
End With
-------------------------------------------------------------------
Dave

Tommy

Tommy,

The quickest way of working on values is to
1) Create a working column of VBA formulae, or
2) USe a variant array  (see my article at https://www.experts-exchange.com/A_2684.html)

The varinat array code below runs on column A (from first to last used call) in the activesheet. As a sample it converts
xy
into xy-xy
inside the array then dumps the range back when done

If you want to work on cell formatting rather than value manipulation then a different approach is needed


Cheers

Dave
Sub REDo()
    Dim rng1 As Range
    Dim lngRow As Long
    Dim x
    Set rng1 = Range([a1], Cells(Rows.Count, "A").End(xlUp))
    x = rng1
    For lngRow = 1 To UBound(x)
        x(lngRow, 1) = x(lngRow, 1) & "-" & x(lngRow, 1)
    Next
    rng1 = x
End Sub

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
tommym121

Dim Cell As Object
For Each Cell In ActiveCell.EntireColumn
          ' Do something on each cell in the column
          If not Cell.Value = nothing Then
         
          End If
 Next Cell

I am trying to do above but the cell is not reference to each cell in the column but rather then the entire column.  It loops through once only.  What can I do to make it examine each cell.

ASKER CERTIFIED SOLUTION
Dave

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
tommym121

Thanks.