PDA

View Full Version : Macro to Group columns on the basis of criteria



abhay_547
06-27-2010, 04:54 AM
Hi All,

I am currently trying to write a macro which will group columns in a specified range on the basis of a criteria for e.g. I have a range from Columns A to D then my macro will be looking into the 4th row of each column and see if there is a zero value or blank in the same if for e.g. If A4 has zero value and B4 also has zero value then it will group A4 and B4 columns together or if only A4 has value zero then it will hide A column and this way it will loop through the entire column range specified and do the grouping. Following is the code which I have currently with me.

Sub AutoGroup()

Application. ScreenUpdating = False

Dim intFirstColumn As Integer
Dim intLastColumn As Integer


intFirstColumn = 0

'Format rows and columns
Columns("B:Z").Select


'Set AutoFilter


Range("B4").Select

Do Until ActiveCell.Value <> 0

If ActiveCell.Value = ActiveCell.Offset(0, 1).Value Then
'First Match
If intFirstColumn = 0 Then intFirstColumn = ActiveCell.Row + 1
'If not the first match, do nothing. Continue on.
Else
'If intFirstColumn is not 0, and the next cell does not match,
'we've reached the end of the group. Create the group.
If intFirstColumn <> 0 Then
intLastColumn = ActiveCell.Row
Columns(intFirstColumn & ":" & intLastColumn).Select
Selection.Columns.Group
intFirstColumn = 0
Range("A" & intLastColumn).Select
End If
'If intFirstColumn = 0, and the next cell does not match, this
'cell is unmatched, and there is no group. Continue on.

End If

ActiveCell.Offset(0, 1).Select

Loop



Application.ScreenUpdating = True
End Sub

Thanks a lot for your help in advance.:bow:

p45cal
06-27-2010, 05:12 AM
please mention when you cross-post where you have done so..
so far here:
http://www.mrexcel.com/forum/showthread.php?t=477306
anywhere else?