select range in columns

[Wim Geuens]

hello,

can someone tell me how i can search in a column range.
I can make it work for the rows, but not for the colums

thanks!
WIm

Public Sub Group()
 
Dim nColumn As Long
Dim nStart As Long, nEnd As Long
Dim i As Integer
 
i = 1
 
Do
 
For nColumn = A To AAA
If Range(nColumn & "4").Value = i Then
nStart = nColumn
Exit For
End If
Next nColumn
 
For nColumn = nStart To AAA
If Range(nColumn & "4").Value <> i Then
nEnd = nRow
Exit For
End If
Next nColumn
nEnd = nEnd - 1
 
Columns(nStart & ":" & nEnd).Select
Selection.Columns.Group
 
i = i + 1
 
Loop Until i = 52
  
End Sub

 

[Colin Legg]

Hi Wim,

If you want to use a Long variable to loop through the columns then you need to use column numbers rather than column letters:

    For nColumn = 1 To 703
        If Cells(4, nColumn).Value = i Then

If you want to use a Range object to loop through the columns then you can use a For Each...Next loop:

    Dim rngCell As Range
   
    For Each rngCell In Range("A4:AAA4").Cells
        If rngCell.Value = i Then

        'etc...
        End If
    Next rngCell

 

[Marc L]

Hi,

fastest with Find method (see its help) …
​

 

[Wim Geuens]

Hi Colin,

Thanks for the reply.

But can you tell me how I can select the colums of those cells.
I need to select them in order to group them

Columns(nStart & ":" & nEnd).Select
Selection.Columns.Group

 

[Colin Legg]

Hi,

There are various syntaxes/properties you could use; here's one of them:

Range(Cells(4, nStart), Cells(4, nEnd)).Group