Excel Code for looping

[jonnj]

Hi Everyone.

I need help with some Vba code.

I have a spreadsheet and column E will have text of either A, B or C. After I sort the column in ascending order, I want a marcro that will find the last cell with A , Then add a blank row, then find the last cell with B and add a blank row.

Can anyone steer me in the right direction or have a code like this.

Thx in advance
John

 

[xlbo]

Sounds like a subtotal issue to me....if I'm right, the subtotal wizard will do this for you - to get the code, just record yourself doing it...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[jonnj]

Geoff,

I tried doing that, however I dont know how many A's or B's I will have. When I recorded the marcro it set the range which I know can change, but how do I do record the macro to find the last A or the last B.? It could be different every time and the insertion of blank row has to be at the end of that tha particular text.

Thx
John

 

[xlbo]

Hi John - can I confirm that it is for putting subtotals in ??

If so, you don't need to know the positions - you specify which column to sum etc on a change of value and excel will do it all for you..

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi John,

I suspect Geoff is right and you want subtotals on column E but, if not, what are you trying to achieve? If I understand correctly you could have:

[tt]| A | B | C | D | E |
+-----+-----+-----+-----+-----+
| 1 | | | | 1 |
| 1 | | | | 1 |
| | 1 | | | 1 |
| | 2 | | | 2 |
| 3 | | | | 3 |
| 3 | | | | 3 |
| | | 4 | | 4 |
| | | 5 | | 5 |
| | 6 | | | 6 |
| | 6 | | | 6 |
| 6 | | | | 6 |
| | | | | |
| | | | | |
| | | | | |[/tt]

and would want to get ..

[tt]| A | B | C | D | E |
+-----+-----+-----+-----+-----+
| 1 | | | | 1 |
| 1 | | | | 1 |
| | 1 | | | 1 |
| | 2 | | | 2 |
| 3 | | | | 3 |
| 3 | | | | 3 |
| | | 4 | | 4 |
| | | 5 | | 5 |
| | | | | |
| | 6 | | | 6 |
| | 6 | | | 6 |
| | | | | |
| 6 | | | | 6 |
| | | | | |
| | | | | |
| | | | | |
| | | | | |[/tt]

If so, subtotals won't do it. There are at least two FAQs on finding the last used row, but here is a simple loop to do the above

For col = 1 To 3
    Cells(65536, col).End(xlUp).Offset(1).EntireRow.Insert
Next

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[jonnj]

Thx Geoff and Tony,

The subtotals and that short code work for me..

John