xldown and redefine the column name macro

shingo108

New member
Joined
Jun 23, 2011
Messages
2
Reaction score
0
Points
0
Good day Guys , I am new to here and seeking great help from you guys, I need a macro that , when i toggle the macro, it will go to every column xldown and redefine the column name using the top name like alpha beta and carrot, if i input another info in the column it will auto redefine it to group it to the same column name....

alpha | Beta | Carrot
asd |asd | asd
asd |asd | asd
asd |asd |
asd | |

i want to be when i input new data to the existing column, it will select until the xldown and redefine the column name by using the top name....

Hope to get the answer from you guyz soon...
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Code:
Dim Lastrow As Long, Lastcol  As Long
Dim i As Long

    With ActiveSheet
    
        Lastcol = .Range("A1").End(xlToRight).Column
        For i = 1 To Lastcol
        
            Lastrow = .Cells(1, i).End(xlDown).Row
            .Names.Add Name:=.Cells(1, i).Value2, RefersTo:="=OFFSET(" & .Cells(1, i).Address & ",0,0,COUNTA(" & .Columns(i).Address & "),1)"
        Next i
    End With
 

shingo108

New member
Joined
Jun 23, 2011
Messages
2
Reaction score
0
Points
0
I am getting run time error "1004"

the situation now is my data all in sheet 2

i use =INDIRECT(CONCATENATE(B21,"_M")) to link my sheet 2 data , i needed a code that will able to toggle the macro that when i input new data to the sheet 2 ,it will automatically redefine the whole column until x1down to the name at the top column

i am still new in this , sorry for any inconvenience and thanks for reply :confused2:
 

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
Why not attach a sample workbook, this way we can actually see your data type and structure and tailor something toward that :)
 
Top