Need to Group the columns in the excel sheet

Help Activities
, , ,
1

Hi All,

I need to select the columns from C4 : last column -1 in the excel sheet and then i need to deselect few columns from the selected range and then group the columns which are selected as single column. The actions which needs to be performed is on pivot table sheet. Please refer the screenshot below

image
image1366×436 32.7 KB

Required final output screenshot:
image
image1365×464 36.2 KB

How can i achieve it the columns that to selected to deselect are dyanamic

2

@vishal_nachankar

Please use a invoke vba…you can pass the required start and wnd dynamically

Also to know read the data into datatable and decide the atart and end using whatever logic you want to define and then pass the start and end to the vba

Cheers

3

the column which i need to deselect are different indexes

4

@vishal_nachankar

Whichever it is there should be some logic to give start and end manually also …use the same as your logic to determine them

Cheers

5

Yes but for eg the columns which i need to delect are D4,h4,Z4 then how it will work i am not good at VBA so need help

6

I can share the file if you want…

7

@vishal_nachankar

Could create this vba for passing cell numbers you need

Sub GroupColumns(cellNumbers As String)
    Dim cellArray() As String
    Dim cellRef As String
    Dim columnRange As Range
    
    ' Split the input string into an array of cell references
    cellArray = Split(cellNumbers, ",")
    
    ' Loop through each cell reference and group columns in the pivot table
    For Each cellRef In cellArray
        ' Convert cell reference to column range
        Set columnRange = Range(cellRef).EntireColumn
        With ActiveSheet.PivotTables("PivotTable3").PivotFields("Years")
            .LabelRange.Cells(1, 2).Group Start:=(Excel.Application.WorksheetFunction.EoMonth(columnRange, -2) + 1), End:=Excel.Application.WorksheetFunction.EoMonth(columnRange, 11), Periods:=Array(False, False, False, False, True, False, True)
            .Position = 1
        End With
    Next cellRef
End Sub

Note : help from chatgpt here

Cheers

8

i need to pass the cell numbers which i need to deselect right?

9

because the columns that i need to select will be more every time but the columns which i need to deselect will be around 5-6 only

10

I have tried this below macro but it is giving error at step Selection.Group
Sub FinalMacro(RangeToSelect As String, DeselectRange As String)
Range(“C4:” & RangeToSelect).Select
For Each cell In DeselectRange
cell.Select
Selection.Interior.ColorIndex = xlNone ’ Remove any cell color
Next cell
Selection.Group
Range(“C4”).Select
ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Loadg Date2”).PivotItems( _
“Group1”).Caption = “Backlog”
Range(“C4”).Select
ActiveSheet.PivotTables(“PivotTable1”).PivotSelect “Backlog”, xlDataAndLabel, _
True
ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Loadg Date2”).ShowDetail = _
False
End Sub

11

@vishal_nachankar

The formula I pasted above and what you are using looks compeltely different…try to use the above one

Its better to pass what you want to select…even they are more

Cheers