Replace Range by Cells

[jocker_boy]

Hello,

I'm trying to create a column group based on my cells that are empty in row 4.

I have this code working perfect:

Sub MakeGroups()
    Dim rA As Range

    For Each rA In Range("ED4", Range("ED4").End(xlToRight)).SpecialCells(xlCellTypeBlanks)
        rA.EntireColumn.Group
    Next rA
End Sub

But my column name is variable, so i would like to replace with this:

Sub MakeGroups()
    Dim rA As Range
    Dim lc As Long
      
'Find last column in row 1 with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
 
    For Each rA In Range(Cells(4, lc + 1), Range(Cells(4, lc + 1)).End(xlToRight)).SpecialCells(xlCellTypeBlanks)
        rA.EntireColumn.Group
    Next rA
End Sub

But it doesn't work.

Thanks for all the help.
Gonçalo

 

[Joe4]

I don't understand.
If you are starting from the last populated column in a row, moving to the right will not find anything, it will simply go to the last possible column on the worksheet .

Maybe you could show us an example (with data) of what you are hoping to accomplish here.

 

[jocker_boy]

I don't understand.
If you are starting from the last populated column in a row, moving to the right will not find anything, it will simply go to the last possible column on the worksheet .

Maybe you could show us an example (with data) of what you are hoping to accomplish here.

Header1	Header2
xx	xx
xx	xx
xx	xx
xx	xx				xx
xx	xx	xx	xx	xx	xx

In this example i need to group columns 3 to 5. because in row 4 they are empty.
The variable is my number of columns with header. In this example i have 2 columns but i can have 20 or more.
And also the number of columns to group are variable.

My goal is to check what is the last column with data in row1 - variable "lc"
Go to row 4 and start after "lc" and finish until have data in row 4.

Thanks

 

[Joe4]

In this example i need to group columns 3 to 5. because in row 4 they are empty.

I don't see how you arrive there from your example.

The last column with data in row 1 is column 2.
The last column with data in row 4 is still column 2.
The last column with data in row 5 is column 6.

So how do you get that columns 3-5 need to be grouped for row 4?

 

[jocker_boy]

I don't see how you arrive there from your example.

The last column with data in row 1 is column 2.
The last column with data in row 4 is still column 2.
The last column with data in row 5 is column 6.

So how do you get that columns 3-5 need to be grouped for row 4?

Header1	Header2
xx	xx
xx	xx
xx	xx
xx	xx				DATA
xx	xx	xx	xx	xx	xx

The last column with data in row 4 is column 6.
Sorry for this confusion.

In this example, i can use this code and works perfect.

Sub MakeGroups()
    Dim rA As Range

    For Each rA In Range("C4", Range("C4").End(xlToRight)).SpecialCells(xlCellTypeBlanks)
        rA.EntireColumn.Group
    Next rA
End Sub

But column "C" is variable. It is always the last column with data in row 1 plus 1.

 

[Joe4]

OK, see if this gives you what you are looking for:

Sub MakeGroups()

    Dim rA As Range
    Dim lc1 As Long
    Dim lc4 As Long
    
'   Find last column with data in row 1
    lc1 = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Find last column with data in row 4
    lc4 = Cells(4, Columns.Count).End(xlToLeft).Column

    For Each rA In Range(Cells(4, lc1 + 1), Cells(4, lc4 - 1)).SpecialCells(xlCellTypeBlanks)
        rA.EntireColumn.Group
    Next rA
    
End Sub

 

[jocker_boy]

OK, see if this gives you what you are looking for:

Sub MakeGroups()

    Dim rA As Range
    Dim lc1 As Long
    Dim lc4 As Long
   
'   Find last column with data in row 1
    lc1 = Cells(1, Columns.Count).End(xlToLeft).Column
   
'   Find last column with data in row 4
    lc4 = Cells(4, Columns.Count).End(xlToLeft).Column

    For Each rA In Range(Cells(4, lc1 + 1), Cells(4, lc4 - 1)).SpecialCells(xlCellTypeBlanks)
        rA.EntireColumn.Group
    Next rA
   
End Sub

Thanks you very much.
It's perfect.

 

[Joe4]

You are welcome.
Glad I was able to help!