Grouping data inbetween columns

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
I have 10 columns of data. In the middle of those columns somewhere are the "Weekly Sales" column and an "Employee" column. I need the columns inbetween "Weekly Sales" and "Employee" grouped and hidden.(not including "Weekly Sales and "Employee" columns in the grouping). When I run my Macro, I do not know which columns the data will be in. All I know is that column headings are in row one. Any help will be appreciated. Thanks!!!!!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Try this:
Code:
Sub Hide()
Dim rCell As Range

For Each rCell In Range(Rows("1:1").Address)
    If rCell = "Weekly Sales" Then
        counter = 1
        Do Until rCell.Offset(0, counter) = "Employee"
            rCell.Offset(0, counter).EntireColumn.Hidden = True
            counter = counter + 1
        Loop
    End If
Next

End Sub
Dufus
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try
Code:
Sub test()
Dim WeeklySales As Range, Employee As Range
On Error Resume Next
Set WeeklySales = Rows(1).Find("Weekly Sales").Offset(,1)
Set Employee = Rows(1).Find("Employee").Offset(,-1)
Range(WeeklySales,Employee).EntireColumn.Hiden = True
End Sub
 

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
Thanks!!! It worked...One more question. Lets say I want two seperate groupings beween those two columns. The first grouping would be the last 4 columns before "Employee" (not including "Employee"). The second grouping would be the rest of the columns in between "Weekly sales" and "Employee" (also not including "weekly sales" in the grouping). I guess in VB terms, the second grouping would be--the count of columns in between "Weekly Sales and "Employee" minus 4. Thanks a bunch !!! This is great
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try
Code:
Sub test()
Dim WeeklySales As Range, Employee As Range
On Error Resume Next
Set WeeklySales = Rows(1).Find("Weekly Sales").Offset(,1)
Set Employee = Rows(1).Find("Employee").Offset(,-1)
x = Range(WeeklySales,Employee).Columns.Count + 1
Employee.Offset(,-3).Resize(,4).EntireColumn.Hidden = True
WeeklySales.Resize(,x-4+1).EntireColumn.Hidden = True
End Sub
 

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
Thanks guys. I have enough code to take it from here. Really appreciate it guys. Take care
 

Watch MrExcel Video

Forum statistics

Threads
1,114,388
Messages
5,547,661
Members
410,805
Latest member
Ginoji
Top