Forum Discussion
How to group automatically in excel
Sekoleyte Interesting. So I found an easier and better way to do the WBS level:
=(SEARCH(LEFT(TRIM(B2),1),B2)-1)/2+1This version will NOT be affected by extra spaces at the end or in the middle 🙂
I also created a macro to automatically do the grouping based on whichever column you highlight:
Public Sub CreateGrouping() '(Optional c As Range) ', Optional UseSpaces = 0) Dim i, r, g As Integer Dim cc As Range If VarType(c) = vbObject Then Set cc = c Else Set cc = Selection End If r = Intersect(cc, ActiveSheet.UsedRange).Rows.Count 'use intersect incase they pass entire column For i = 1 To r If IsNumeric(cc.Cells(i, 1).Value2) Then g = cc.Cells(i, 1).Value2 j = i + 1 If cc.Cells(j, 1) > g Then Do While (cc.Cells(j + 1, 1).Value2 > g) And (j < r) j = j + 1 Loop Range(cc.Cells(i + 1, 1), cc.Cells(j, 1)).EntireRow.Group End If End If Next i End Sub
Sekoleyte Interesting. So I found an easier and better way to do the WBS level:
=(SEARCH(LEFT(TRIM(B2),1),B2)-1)/2+1This version will NOT be affected by extra spaces at the end or in the middle 🙂
I also created a macro to automatically do the grouping based on whichever column you highlight:
Public Sub CreateGrouping() '(Optional c As Range) ', Optional UseSpaces = 0)
Dim i, r, g As Integer
Dim cc As Range
If VarType(c) = vbObject Then
Set cc = c
Else
Set cc = Selection
End If
r = Intersect(cc, ActiveSheet.UsedRange).Rows.Count 'use intersect incase they pass entire column
For i = 1 To r
If IsNumeric(cc.Cells(i, 1).Value2) Then
g = cc.Cells(i, 1).Value2
j = i + 1
If cc.Cells(j, 1) > g Then
Do While (cc.Cells(j + 1, 1).Value2 > g) And (j < r)
j = j + 1
Loop
Range(cc.Cells(i + 1, 1), cc.Cells(j, 1)).EntireRow.Group
End If
End If
Next i
End Sub
Thank you so much. I could checked your solutions just now. I appreciate you for your approaches and solutions. I couldn't study on your macro yet but it works great! I like that kind of life saver solutions. I really respect you. Thank you for your help! Probably this will be my start to learning coding.
And thanks to all other helper guys!
mtarler Thank you.
To find WBS level, you found new and easier approach. That looks awesome.
For your macro solution i will check it detailed and try to understand and learn it. i wonder how it works.
But now i doubt something that goes wrong for my computer.
and that QR code directed me to that site:
https://support.microsoft.com/en-us/sbs/windows/troubleshoot-blue-screen-errors-5c62726c-6489-52da-a372-3f73142c14ad
A few days ago while i am checking your solutions, i got this message. After restart, i couldn't connect the internet and when i open the office it gave me an error message to repair and then it always closed itself. Then i gave my company computer to my IT. They couldn't solve the problem and they formatted my computer because they said that my windows doesn't work normal. Now i got this message in this morning when i open my computer and i could take this photo.
I am the only one who uses O365 in my company now. Others use Office 2019. Because i used O365 in my ex company, i insisted to have it individually. So they let me set up O365 instead of Office 2019. Our IT member suspects whether O365 cause it or not. I don't think it is because of O365. Did you experience same issue with your own computers? Do we have anything in those documents which can cause this situation? I don't want they take my O365 from me 😞
Riny_van_Eekelen Lorenzo mtarler
- I do not think that has to do with O365. In my experience, blue screen errors are due to driver or hardware issues. Other than that, I'm not much more help.