Excel Extend Cell Range for Current Row

[PWD]

My user has sorted data with subtotals breaking on Column E. He wants to copy the data from columns a to d of the last line before each break and paste it in to the subtotal row so that when he collapses the selection the subtotal figures have a meaningful label. I was considering starting at cell A1, using a loop with 'Selection.End(xlDown).Select' to get to the last line in each section but I'm having trouble with the sytax for either extending the current selection across by 3 cells or using some sort of 'Range("a(CurrentRow):d(CurrentRow)&quot.Select'. I would then copy, go down one row and back to Column A and paste in the selection using ActiveSheet.Paste back to Column A again and carry on wqith the loop. Any (KISS) ideas?
Des.

 

[Loomah]

Hi
I'm not really sure what you are trying to achieve here but to answer your question(s) in isolation you're almost there

eg

lRow=range("A1").end(xldown).ROW

gets you the row number you need to work with - the last row containing data.

range(cells(lRow,1),cells(lRow,4)).copy

will copy the data in the row you have just found from columns A to D.

Note there is no need to select in either case. Have a look at the help for COPY to see about using the 'destination' argument/parameter so there is no need to paste (just use the range syntax above and add 1 to the row)

Please note I've written this code directly into this post so there may be a typo but I'd hope not!

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[PWD]

Thanks. I have a spreadsheet sorted by Column E into which I’ve put subtotals. When you collapse the view down to level 2 and only see the subtotals you can see what it’s breaking on but no data from the other columns. What I now have is code that will take the data from above the break point and paste it in to the break/subtotal row. Then when the view is collapsed there’s some information in addition to the subtotals. That’s what my user wants!!

A B C D Number
a b c d 12345
a b c d 12345
a b c d 12345
a b c d 12345
a b c d 12345
12345 Count 5
e f g h 12346
e f g h 12346
e f g h 12346
e f g h 12346
e f g h 12346
12346 Count 5
I j k l 12347
I j k l 12347
I j k l 12347
I j k l 12347
I j k l 12347
12347 Count 5
m n o p 12348
m n o p 12348
m n o p 12348
m n o p 12348
m n o p 12348
12348 Count 5
Grand Count 20

Application.ScreenUpdating = False

Range("A1&quot.Select
Do
Selection.End(xlDown).Select
Selection.Resize(1, 3).Copy
On Error GoTo line999 'Because the final xlDown
'goes to line 65536 and you can't go down from there
Selection.Offset(1, 0).Select
ActiveSheet.Paste

Loop
line999: Range("A1&quot.Select

Application.ScreenUpdating = True

A B C D Number
a b c 12345 Count 5
e f g 12346 Count 5
I j k 12347 Count 5
m n o 12348 Count 5
Grand Count 20
It's still a bit too 'Selecty' for my liking but this is the sort of code I couldn't find yesterday that DOES work for me.

Des.

 

[Loomah]

Des
Glad you have your solution!
Now that I have seen what you were after this bit of code may clarify what I was saying before. This isn't 'selecty!'

Sub FillSubTotGaps()
Dim lRow As Long
Dim lRow2 As Long
lRow2 = Range("A65536").End(xlUp).Row
lRow = Range("A1").End(xlDown).Row
Do While lRow < lRow2 + 1
    With Range(Cells(lRow, 1), Cells(lRow, 3))
        .Copy Destination:=Cells(lRow + 1, 1)
        lRow = .End(xlDown).Row
    End With
Loop
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[PWD]

V V neat. I'll certainly try this sort of thing in the future.

Des.