Hi guys, I've been lurking around for awhile and getting answers, but I appear to be stuck on something that I can't find a solution for. I'm trying to create some groupings of rows, and I was struggling to do that, so I ended up having to resort to a SendKeys command. That's been working fine for awhile, but I have to hit the macro for each group. I'm now trying to automate it so I can just hit it once and it'll do it all for me. However, from what I can gather trying to mess around with it, the SendKeys commands don't get sent until after the macro has finished running. As such, I'm struggling to find a way to reference where I am for the do loop.
My excel sheet ends up with 3 layers of groups... I have an overall group of a specific category, which is marked by a *, and then each individual function within the category has a row and is labeled with a ranking, and each issue within each function within the category has a row but no ranking. I am attempting to group all of the individual issues within a function together (and then the next layer of grouping groups the functions together into the single category).
So, my initial code was
If ActiveCell.Value = "*" Then
SendKeys "{DOWN}", True
End If
SendKeys "{DOWN}", True
SendKeys "^+{DOWN}", True
SendKeys "+{UP}", True
SendKeys "+%{RIGHT}", True
SendKeys "{ENTER}", True
SendKeys "^{DOWN}", True
And what it does is identifies if it's on the main category (via checking for the *) and if it is, then it goes down a row, if not it stays where it is. From there, it goes down a row, ctrl+shift+down to highlight until the next ranking, shift+up to exclude that ranking, shift+alt+right to open the grouping menu, enter to select group rows, and ctrl+down to get to the next ranking.
I want to stop when I reach two * rows in a row, indicating that there's nothing between the two. I figured that would be accomplished by the following:
Do While i = 1
If ActiveCell.Value = "*" Then
SendKeys "{DOWN}", True
End If
If ActiveCell.Offset(1, 0).Value = "*" Then
Exit Do
End If
SendKeys "{DOWN}", True
SendKeys "^+{DOWN}", True
SendKeys "+{UP}", True
SendKeys "+%{RIGHT}", True
SendKeys "{ENTER}", True
SendKeys "^{DOWN}", True
Loop
It makes logical sense to me, but messing around with it, it seems that the SendKeys doesn't happen until after the macro has "fully executed" or something like that (if I just put a counter = 1000 on it, it'll wait around for awhile, and then scream through grouping everything).
I know SendKeys is an awful way to do this, but since I have various amounts of rows that need to be grouped, I'm not quite sure how else to do it. Either thoughts on how I can get around this shortcoming or thoughts on how to replace SendKeys with some other method of grouping?
My excel sheet ends up with 3 layers of groups... I have an overall group of a specific category, which is marked by a *, and then each individual function within the category has a row and is labeled with a ranking, and each issue within each function within the category has a row but no ranking. I am attempting to group all of the individual issues within a function together (and then the next layer of grouping groups the functions together into the single category).
So, my initial code was
If ActiveCell.Value = "*" Then
SendKeys "{DOWN}", True
End If
SendKeys "{DOWN}", True
SendKeys "^+{DOWN}", True
SendKeys "+{UP}", True
SendKeys "+%{RIGHT}", True
SendKeys "{ENTER}", True
SendKeys "^{DOWN}", True
And what it does is identifies if it's on the main category (via checking for the *) and if it is, then it goes down a row, if not it stays where it is. From there, it goes down a row, ctrl+shift+down to highlight until the next ranking, shift+up to exclude that ranking, shift+alt+right to open the grouping menu, enter to select group rows, and ctrl+down to get to the next ranking.
I want to stop when I reach two * rows in a row, indicating that there's nothing between the two. I figured that would be accomplished by the following:
Do While i = 1
If ActiveCell.Value = "*" Then
SendKeys "{DOWN}", True
End If
If ActiveCell.Offset(1, 0).Value = "*" Then
Exit Do
End If
SendKeys "{DOWN}", True
SendKeys "^+{DOWN}", True
SendKeys "+{UP}", True
SendKeys "+%{RIGHT}", True
SendKeys "{ENTER}", True
SendKeys "^{DOWN}", True
Loop
It makes logical sense to me, but messing around with it, it seems that the SendKeys doesn't happen until after the macro has "fully executed" or something like that (if I just put a counter = 1000 on it, it'll wait around for awhile, and then scream through grouping everything).
I know SendKeys is an awful way to do this, but since I have various amounts of rows that need to be grouped, I'm not quite sure how else to do it. Either thoughts on how I can get around this shortcoming or thoughts on how to replace SendKeys with some other method of grouping?
